MariaDB has the FORMAT() function, which formats a number based on the given format string.

It returns the number as a formatted string, with thousands and decimal separators in the appropriate position and rounds the result to the specified decimal position.

You can optionally specify a locale value to format numbers to the pattern appropriate for the given region.

Example

Here’s an example to demonstrate:

SELECT FORMAT(123456.789, 2);

Result:

123,456.79

In this case I specified 2 for the second argument, and so the number was rounded to two decimal places.

Also, a comma was inserted for the thousands separator, and a period/full stop was used for the decimal separator.

Here’s another example that uses different values for the second argument:

SELECT 
    FORMAT(123456.789, 0) AS "1",
    FORMAT(123456.789, 4) AS "2",
    FORMAT(123456.789, 6) AS "3";

Result:

+---------+--------------+----------------+
| 1       | 2            | 3              |
+---------+--------------+----------------+
| 123,457 | 123,456.7890 | 123,456.789000 |
+---------+--------------+----------------+

Specify a Locale

Here are some examples of specifying the locale:

SELECT 
    FORMAT(123456.789, 2, 'ta_IN') AS "Tamil, India",
    FORMAT(123456.789, 2, 'de_DE') AS "German, Germany",
    FORMAT(123456.789, 2, 'zh_HK') AS "Chinese, Hong Kong";

Result:

+--------------+-----------------+--------------------+
| Tamil, India | German, Germany | Chinese, Hong Kong |
+--------------+-----------------+--------------------+
| 1,23,456.79  | 123.456,79      | 123,456.79         |
+--------------+-----------------+--------------------+

See How to Show all Locales in MariaDB to get a list of locales that can be specified with this function.

Formatting as Currency

The FORMAT() function doesn’t provide the means to format the number as currency. However, you can use the CONCAT() function to concatenate the results with the currency symbol of your choice:

SELECT CONCAT('$', FORMAT(8790.2398, 2));

Result:

$8,790.24

This obviously requires that you specify the correct currency symbol for the locale being used.