Format amount values for thousands number with two decimal places in MySQL?


For thousands number, use MySQL FORMAT(). Let us first create a −

mysql> create table DemoTable1394
   -> (
   -> Amount decimal(7,3)
   -> );
Query OK, 0 rows affected (0.68 sec)

Insert some records in the table using insert −

mysql> insert into DemoTable1394 values(60);
Query OK, 1 row affected (0.15 sec)
mysql> insert into DemoTable1394 values(2355.4);
Query OK, 1 row affected (0.27 sec)
mysql> insert into DemoTable1394 values(456);
Query OK, 1 row affected (0.12 sec)
mysql> insert into DemoTable1394 values(8769);
Query OK, 1 row affected (0.13 sec)

Display all records from the table using select −

mysql> select * from DemoTable1394;

This will produce the following output −

+----------+
| Amount   |
+----------+
|   60.000 |
| 2355.400 |
|  456.000 |
| 8769.000 |
+----------+
4 rows in set (0.00 sec)

Here is the query to format for thousands number with two decimal places −

mysql> select format(Amount,2) from DemoTable1394;

This will produce the following output −

+------------------+
| format(Amount,2) |
+------------------+
| 60.00            |
| 2,355.40         |
| 456.00           |
| 8,769.00         |
+------------------+
4 rows in set (0.00 sec)

Updated on: 11-Nov-2019

142 Views

Kickstart Your Career

Get certified by completing the course

Get Started
Advertisements