How FORMAT() Works in MariaDB

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