Skip to content

FORMAT()

Function Description

The FORMAT function is used to set the number format to "#,###,##.##" format and round to one decimal point. After formatting the number, it returns the value as a string.

grammar

> FORMAT(X,D[,locale])

Parameter definition

Parameters Description
X Required parameters. X is the number to be formatted. If X is NULL, the function returns NULL.
D Required parameters. D is to round the number of decimal places
If D is 0, the result is no decimal point or no decimal part.
If D is NULL, the function returns NULL.
[,locale] Optional parameters. Optional parameter [,locale] specifies the locale to use and is used to determine the grouping between the thousand separators and the separators. If [locale] is set to NULL or not specified, the default locale is 'en_US'. [,locale] supports locale parameters consistent with MySQL, see MySQL Server Locale Support.

Example

mysql> SELECT FORMAT(12332.123456, 4);
+------------------------------+
| format(12332.123456, 4) |
+------------------------------+
| 12,332.1235 |
+------------------------------+
1 row in set (0.01 sec)

mysql> SELECT FORMAT(12332.1,4);
+-------------------------+
| format(12332.1, 4) |
+-------------------------+
| 12,332.1000 |
+-------------------------+
1 row in set (0.00 sec)

mysql> SELECT FORMAT(12332.2,0);
+-------------------------+
| format(12332.2, 0) |
+-------------------------+
| 12,332 |
+-------------------------+
1 row in set (0.00 sec)

mysql> SELECT FORMAT(12332.2,2,'de_DE');
+--------------------------------+
| format(12332.2, 2, de_DE) |
+--------------------------------+
| 12.332,20 |
+--------------------------------+
1 row in set (0.00 sec)

mysql> SELECT FORMAT(19999999.99999999,4);
+------------------------------------+
| format(19999999.99999999, 4) |
+------------------------------------+
| 20,000,000.0000 |
+------------------------------------+
1 row in set (0.01 sec)

mysql> SELECT FORMAT("-.12334.2","2", "en_US");
+----------------------------------+
| format(-.12334.2, 2, en_US) |
+----------------------------------+
| -0.12 |
+----------------------------------+
1 row in set (0.00 sec)

mysql> SELECT FORMAT("-.12334.2","2", "de_CH");
+----------------------------------+
| format(-.12334.2, 2, de_CH) |
+----------------------------------+
| -0.12 |
+----------------------------------+
1 row in set (0.01 sec)