We can use the FORMAT() function in MySQL to format numbers into a more human readable format.

The function returns the number as a formatted string. It adds thousands separators and a decimal separator as required, and rounds the results to the given decimal position.

Example

In MySQL, the FORMAT() function accepts a number as the first argument, followed by the decimal position:

SELECT FORMAT(123456.789, 2);

Result:

123,456.79

There’s no need to specify where the commas should go. The function knows where to put them.

Here’s another example with a larger number:

SELECT FORMAT(123456789.123456789, 6);

Result:

123,456,789.123457

Using a Comma for the Decimal Place

In many locales, the comma is used as the decimal place, and the period/full stop is used for the group separator.

The FORMAT() function accepts a third argument which allows us to specify the locale to use when formatting the number.

Example:

SELECT FORMAT(123456.789, 2, 'de_DE');

Result:

123.456,79

In this case, I set the locale to de_DE, which is for German. This time the comma is used as the decimal separator, and the full stop is used for the group separator.