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)