In MySQL, the FORMAT()
function returns a number formatted to a specified number of decimal places.
It includes group separators and a decimal separator where applicable.
Syntax
The syntax goes like this:
FORMAT(X,D[,locale])
Where:
X
is the number to format.D
is the number of decimal places you want to round the number to.locale
is an optional argument that specifies which locale’s formatting conventions to use.
Example
Here’s an example to demonstrate:
SELECT FORMAT(123456.789, 2);
Result:
123,456.79
Here, the number was rounded to two decimal places (because I specified 2
as the second argument). In this case, the decimal part was rounded up.
The number also got a comma as the group separator, and a period/full stop as the decimal separator.
Remove the Fractional Part
You can use 0
as the second argument to remove any fractional part:
SELECT FORMAT(123456.789, 0);
Result:
123,457
Add Decimal Places
You can add decimal places by using a second argument that’s a larger number than the number of decimal places in the number to be formatted:
SELECT FORMAT(123456.789, 6);
Result:
123,456.789000
This can be used to add a fractional part to integers:
SELECT FORMAT(123456, 2);
Result:
123,456.00
Negative Numbers
The function works just as well on negative numbers:
SELECT FORMAT(-123456.789, 2);
Result:
-123,456.79
Locale
Here’s an example to demonstrate how the third argument works:
SELECT FORMAT(123456.789, 2, 'de_DE');
Result:
123.456,79
In this example, I specified that the locale is de_DE
, which is for German. The formatting conventions therefore dictate that the comma is used for the decimal separator, and the full stop/period is used for the thousands separator.
The third argument can be any of the values that are supported by the lc_time_names
system variable.