In MariaDB, FORMAT() is a built-in string function that formats and returns the given number as a string.
It requires two arguments, and accepts an optional third argument. The first is the number you want to format, the second is the decimal position, and the (optional) third is the locale.
Syntax
The syntax goes like this:
FORMAT(num, decimal_position[, locale])Where num is the number, decimal_position is the number of decimal places to round the number to, and locale is an optional locale to use for formatting the number (different locales use different conventions for formatting numbers).
Example
Here’s a basic example:
SELECT FORMAT(1234.5678, 2);Result:
+----------------------+ | FORMAT(1234.5678, 2) | +----------------------+ | 1,234.57 | +----------------------+
Here, FORMAT() added a comma for a thousands separator. It also rounded the number to two decimal places (because I specified 2 as the second argument).
Remove Fractional Part
To remove the fractional part (and its associated decimal point), use 0 (zero) as the second argument:
SELECT FORMAT(1234.5678, 0);Result:
+----------------------+ | FORMAT(1234.5678, 0) | +----------------------+ | 1,235 | +----------------------+
Adding Decimal Places
If the second argument is a higher number than the number of decimal places in the first argument, zeros are added to the result, so that it reflects the desired number of decimal places:
SELECT
FORMAT(1234, 2),
FORMAT(1234.56, 4),
FORMAT(1234.56, 8);Result:
+-----------------+--------------------+--------------------+ | FORMAT(1234, 2) | FORMAT(1234.56, 4) | FORMAT(1234.56, 8) | +-----------------+--------------------+--------------------+ | 1,234.00 | 1,234.5600 | 1,234.56000000 | +-----------------+--------------------+--------------------+
Specify a Locale
You can optionally pass a third argument to specify the locale to use for the formatting.
Example:
SELECT
FORMAT(1234567.8912, 2, 'da_DK') AS "da_DK",
FORMAT(1234567.8912, 2, 'rm_CH') AS "rm_CH",
FORMAT(1234567.8912, 2, 'es_BO') AS "es_BO",
FORMAT(1234567.8912, 2, 'ar_SA') AS "ar_SA";Result:
+--------------+--------------+------------+------------+ | da_DK | rm_CH | es_BO | ar_SA | +--------------+--------------+------------+------------+ | 1.234.567,89 | 1'234'567,89 | 1234567,89 | 1234567.89 | +--------------+--------------+------------+------------+
Non-Numeric Arguments
FORMAT() only formats numbers. Providing a string, for example, results in 0 being returned. However, it’s still formatted to the specified decimal place:
SELECT
FORMAT("Twenty five", 0),
FORMAT("Twenty five", 2),
FORMAT("Twenty five", 4);Result:
+--------------------------+--------------------------+--------------------------+
| FORMAT("Twenty five", 0) | FORMAT("Twenty five", 2) | FORMAT("Twenty five", 4) |
+--------------------------+--------------------------+--------------------------+
| 0 | 0.00 | 0.0000 |
+--------------------------+--------------------------+--------------------------+
Null Arguments
Providing null as either of the first two arguments results in null:
SELECT
FORMAT(null, 2),
FORMAT(25, null);Result:
+-----------------+------------------+ | FORMAT(null, 2) | FORMAT(25, null) | +-----------------+------------------+ | NULL | NULL | +-----------------+------------------+
But passing null as the third argument does not affect the result:
SELECT FORMAT(25, 2, null);Result:
+---------------------+ | FORMAT(25, 2, null) | +---------------------+ | 25.00 | +---------------------+
Providing Just One Argument
Providing a single argument results in an error:
SELECT FORMAT(1234.56);Result:
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near ')' at line 1
Providing No Arguments
Calling FORMAT() without passing any arguments results in an error:
SELECT FORMAT();Result:
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near ')' at line 1