MySQL Tutorial/Math Numeric Functions/FORMAT

FORMAT(X,D) formats the number X to a format like "#,###,###.##", rounded to D decimal places, and returns the result as a string

   <source lang="sql">

mysql> mysql> create table myTable(

   ->   id           int(2),
   ->   value        FLOAT(6,2)
   -> );

Query OK, 0 rows affected (0.02 sec) mysql> mysql> insert into myTable(ID, value)values (1,9); Query OK, 1 row affected (0.00 sec) mysql> insert into myTable(ID, value)values (2,2.11); Query OK, 1 row affected (0.00 sec) mysql> insert into myTable(ID, value)values (3,312312.44); Query OK, 1 row affected, 1 warning (0.00 sec) mysql> insert into myTable(ID, value)values (4,-123124.21); Query OK, 1 row affected, 1 warning (0.00 sec) mysql> insert into myTable(ID, value)values (5,11231230); Query OK, 1 row affected, 1 warning (0.01 sec) mysql> insert into myTable(ID, value)values (6,3123123); Query OK, 1 row affected, 1 warning (0.02 sec) mysql> insert into myTable(ID, value)values (7,-1231235.88); Query OK, 1 row affected, 1 warning (0.00 sec) mysql> insert into myTable(ID, value)values (8,123.45); Query OK, 1 row affected (0.00 sec) mysql> insert into myTable(ID, value)values (9,934534538.23); Query OK, 1 row affected, 1 warning (0.01 sec) mysql> insert into myTable(ID, value)values (10,934534538.23); Query OK, 1 row affected, 1 warning (0.00 sec) mysql> insert into myTable(ID, value)values (11,-934534584.23); Query OK, 1 row affected, 1 warning (0.00 sec) mysql> insert into myTable(ID, value)values (12,193453458.23); Query OK, 1 row affected, 1 warning (0.00 sec) mysql> insert into myTable(ID, value)values (13,-934534528.87); Query OK, 1 row affected, 1 warning (0.00 sec) mysql> insert into myTable(ID, value)values (14,25.37); Query OK, 1 row affected (0.00 sec) mysql> insert into myTable(ID, value)values (15,-934534518.3); Query OK, 1 row affected, 1 warning (0.00 sec) mysql> insert into myTable(ID, value)values (16,3453459.23); Query OK, 1 row affected, 1 warning (0.00 sec) mysql> insert into myTable(ID, value)values (17,-3453458.23); Query OK, 1 row affected, 1 warning (0.00 sec) mysql> mysql> select * from myTable; +------+----------+ | id | value | +------+----------+ | 1 | 9.00 | | 2 | 2.11 | | 3 | 9999.99 | | 4 | -9999.99 | | 5 | 9999.99 | | 6 | 9999.99 | | 7 | -9999.99 | | 8 | 123.45 | | 9 | 9999.99 | | 10 | 9999.99 | | 11 | -9999.99 | | 12 | 9999.99 | | 13 | -9999.99 | | 14 | 25.37 | | 15 | -9999.99 | | 16 | 9999.99 | | 17 | -9999.99 | +------+----------+ 17 rows in set (0.00 sec) mysql> mysql> select value, format(value,2) from myTable; +----------+-----------------+ | value | format(value,2) | +----------+-----------------+ | 9.00 | 9.00 | | 2.11 | 2.11 | | 9999.99 | 9,999.99 | | -9999.99 | -9,999.99 | | 9999.99 | 9,999.99 | | 9999.99 | 9,999.99 | | -9999.99 | -9,999.99 | | 123.45 | 123.45 | | 9999.99 | 9,999.99 | | 9999.99 | 9,999.99 | | -9999.99 | -9,999.99 | | 9999.99 | 9,999.99 | | -9999.99 | -9,999.99 | | 25.37 | 25.37 | | -9999.99 | -9,999.99 | | 9999.99 | 9,999.99 | | -9999.99 | -9,999.99 | +----------+-----------------+ 17 rows in set (0.00 sec) mysql> mysql> drop table myTable; Query OK, 0 rows affected (0.00 sec) mysql></source>