How to format numbers in MariaDB

In MariaDB, if you want formatted some numbers, use the built-in function FORMAT().

The FORMAT() function formats a number according to the given decimal places and locale. It takes 3 parameters, the first parameter is the number to format, the second parameter is the number of decimal places to keep, and the third parameter is optional, allowing you to pass in a locale value. It includes thousands and decimal separators where appropriate, and rounds the result to the specified number of decimal places.

Example

This example show you how to format 1234.56789 to 4 decimal places:

SELECT FORMAT(1234.56789, 4);

Output:

+-----------------------+
| FORMAT(1234.56789, 4) |
+-----------------------+
| 1,234.5679            |
+-----------------------+

You can specify any decimal places, and the output will be filled with 0 at the back according to the situation, as follows:

SELECT
  FORMAT(1234.56, 0),
  FORMAT(1234.56, 3),
  FORMAT(1234.56, 5);

Output:

+--------------------+--------------------+--------------------+
| FORMAT(1234.56, 0) | FORMAT(1234.56, 3) | FORMAT(1234.56, 5) |
+--------------------+--------------------+--------------------+
| 1,235              | 1,234.560          | 1,234.56000        |
+--------------------+--------------------+--------------------+

Locale

FORMAT() allows you to pass in a locale in the third parameter so that the output conforms to the local format.

The following example demonstrates these:

SELECT
    FORMAT(12345.678, 2, 'de_DE') AS "de_DE",
    FORMAT(12345.678, 2, 'zh_CN') AS "zh_CN";

Output:

+-----------+-----------+
| de_DE     | zh_CN     |
+-----------+-----------+
| 12.345,68 | 12,345.68 |
+-----------+-----------+

To see all locale locale codes supported by MariaDB, refer to the tutorial: Showing All Locales in MariaDB.

Conclusion

In MariaDB, the FORMAT() function can help you format numbers.