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.