MySQL FORMAT() Function

The MySQL FORMAT() function formats a number like '#,###,###.##', rounding it to a specified number of decimal places and then it returns the result as a string.

Syntax

FORMAT(number, decimal_places)

Parameters

`number`	`Required.` Specify the number to format.
`decimal_places`	`Required.` Specify the number of decimal places to round the number.

Return Value

Returns the formatted result as string.

Example 1:

The example below shows the usage of FORMAT() function.

mysql> SELECT FORMAT(12345.6789, 3);
Result: '12,345.679'

mysql> SELECT FORMAT(12345.6789, 2);
Result: '12,345.68'

mysql> SELECT FORMAT(12345.6789, 1);
Result: '12,345.7'

mysql> SELECT FORMAT(12345.6789, 0);
Result: '12,346'

Example 2:

Consider a database table called Employee with the following records:

EmpID	Name	City	Age	Salary
1	John	London	25	3000
2	Marry	New York	24	2750
3	Jo	Paris	27	2800
4	Kim	Amsterdam	30	3100
5	Ramesh	New Delhi	28	3000
6	Huang	Beijing	28	2800

The statement given below can be used to format the records of Salary column of the Employee table:

UPDATE Employee
SET Salary = CONCAT('$ ', FORMAT(Salary, 2));

--see the update
SELECT * from Employee;

This will produce the result as shown below:

EmpID	Name	City	Age	Salary
1	John	London	25	$ 3,000.00
2	Marry	New York	24	$ 2,750.00
3	Jo	Paris	27	$ 2,800.00
4	Kim	Amsterdam	30	$ 3,100.00
5	Ramesh	New Delhi	28	$ 3,000.00
6	Huang	Beijing	28	$ 2,800.00

❮ MySQL Functions