MariaDB FORMAT() Function

In MariaDB, FORMAT() is a built-in string function that rounds a given number to a specified number of decimal places, formats it like #,###,###.##, and returns the result as a string.

MariaDB FORMAT() Syntax

Here is the syntax of the MariaDB FORMAT() function:

FORMAT(number, decimal_places[, locale])

Parameters

number

Required. The number to format.

decimal_places

Required. The number of decimal places to keep.

locale

Optional. Area Codes.

If you do not provide enough parameters, MariaDB will report an error: 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.

Return value

The MariaDB FORMAT() function returns a string similar to the #,###,###.## format (determined by the locale parameter or the current session’s locale) with the specified number of decimal places.

If the number parameter is a character or string, FORMAT() it will try to convert it to a number according to the following rules:

  • If it starts with a number, convert the leading part to a number.
  • If it cannot be converted to a number, treat it as 0.

If the argument number is NULL, the FORMAT() function will return NULL.

If the parameter decimal_places is 0, the content returned by the FORMAT() function has no decimal part.

If the parameter decimal_places exceeds the original number of decimal places, the FORMAT() function will add 0 after the decimal.

MariaDB FORMAT() Examples

Basic example

The following statement shows the basic usage of the MariaDB FORMAT() function:

SELECT FORMAT(12345.6789, 2);

Output:

+-----------------------+
| FORMAT(12345.6789, 2) |
+-----------------------+
| 12,345.68             |
+-----------------------+

Locales

You can use locale to format output according to a specific locale:

SELECT
  FORMAT(12345.6789, 2),
  FORMAT(12345.6789, 2, 'rm_CH');

Output:

+-----------------------+--------------------------------+
| FORMAT(12345.6789, 2) | FORMAT(12345.6789, 2, 'rm_CH') |
+-----------------------+--------------------------------+
| 12,345.68             | 12'345,68                      |
+-----------------------+--------------------------------+

You can see the supported locales in MariaDB.

Padding 0

If the specified number of decimal places exceeds the original number of decimal places, zeros will be added after the decimal place. The following example illustrates this:

SELECT
  FORMAT(12, 4),
  FORMAT(12.34, 4);

Output:

+---------------+------------------+
| FORMAT(12, 4) | FORMAT(12.34, 4) |
+---------------+------------------+
| 12.0000       | 12.3400          |
+---------------+------------------+

Non-numeric

MariaDB FORMAT() will process non-numeric parameters according to the following rules:

  • If it starts with a number, convert the leading part a number.
  • If it cannot be converted to a number, treat it as 0.

The following example illustrates this:

SELECT
  FORMAT('124Abc', 4),
  FORMAT('Abc123', 4),
  FORMAT('Abc', 4);

Output:

+---------------------+---------------------+------------------+
| FORMAT('124Abc', 4) | FORMAT('Abc123', 4) | FORMAT('Abc', 4) |
+---------------------+---------------------+------------------+
| 124.0000            | 0.0000              | 0.0000           |
+---------------------+---------------------+------------------+

In this example, '124Abc' is converted to 124, and 'Abc123' and 'Abc' are converted to 0.

Conclusion

The MariaDB FORMAT() function returns a string similar to the #,###,###.## format (determined by the locale parameter or the current session’s locale) with the specified number of decimal places.