Syntax error MySQL Query to set currency records

MySQL Query to set currency records

Use FORMAT() in MySQL to display currency records and display them in the correct form. Let us first create a table −

mysql> create table DemoTable
   -> (
   -> Amount DECIMAL(15,4)
   -> );
Query OK, 0 rows affected (0.75 sec)

Insert some records in the table using insert command −

mysql> insert into DemoTable values(90948484);
Query OK, 1 row affected (0.13 sec)
mysql> insert into DemoTable values(1000000000);
Query OK, 1 row affected (0.17 sec)
mysql> insert into DemoTable values(1535353536);
Query OK, 1 row affected (0.09 sec)
mysql> insert into DemoTable values(773646463);
Query OK, 1 row affected (0.20 sec)

Display all records from the table using select statement −

mysql> select *from DemoTable;

This will produce the following output −

+-----------------+
| Amount          |
+-----------------+
| 90948484.0000   |
| 1000000000.0000 |
| 1535353536.0000 |
| 773646463.0000  |
+-----------------+
4 rows in set (0.00 sec)

Here is the query to set currency in MySQL −

mysql> select concat('USD ',format(Amount,2)) as Currency from DemoTable;

This will produce the following output. Here, we are displaying USD currency records in the correct format−

+----------------------+
| Currency             |
+----------------------+
| USD 90,948,484.00    |
| USD 1,000,000,000.00 |
| USD 1,535,353,536.00 |
| USD 773,646,463.00   |
+----------------------+
4 rows in set (0.23 sec)
Updated on: 2019-12-17T06:31:26+05:30

263 Views

Kickstart Your Career

Get certified by completing the course

Get Started
Advertisements