SQL Tutorial SQL Advanced SQL Database SQL References

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:

EmpIDNameCityAgeSalary
1JohnLondon253000
2MarryNew York242750
3JoParis272800
4KimAmsterdam303100
5RameshNew Delhi283000
6HuangBeijing282800

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:

EmpIDNameCityAgeSalary
1JohnLondon25$ 3,000.00
2MarryNew York24$ 2,750.00
3JoParis27$ 2,800.00
4KimAmsterdam30$ 3,100.00
5RameshNew Delhi28$ 3,000.00
6HuangBeijing28$ 2,800.00

❮ MySQL Functions