MySQL | FORMAT method
Start your free 7-days trial now!
MySQL's FORMAT(~) method returns a number formatted with thousands separator and rounded to D decimal places.
Parameters
1. X | number
The number to format.
2. D | number
The number of decimal places to round to.
3. locale | lc_time_name | optional
The convention to use for thousands separator and grouping between separators. Defaults to 'en_US'.
Return value
The input number formatted with thousands separator and rounded to D decimal places.
Examples
Decimal parameter
To format 1.2345 to three decimal places:
SELECT FORMAT(1.2345, 3);
+-------------------+| FORMAT(1.2345, 3) |+-------------------+| 1.235 |+-------------------+
Note that the number is now rounded to three decimal places.
Locale parameter
To use default 'en_US':
SELECT FORMAT(10000.123456, 4);
+-------------------------+| FORMAT(10000.123456, 4) |+-------------------------+| 10,000.1235 |+-------------------------+
The locale 'en_US' formats the number with comma thousands separator.
To format the number according to 'de_DE' locale:
SELECT FORMAT(100000000, 0, 'de_DE');
+-------------------------------+| FORMAT(100000000, 0, 'de_DE') |+-------------------------------+| 100.000.000 |+-------------------------------+
With locale 'de_DE' the thousands separator is a dot rather than a comma.
Locale Values
Refer to the below list for valid locale values:
Locale Value | Meaning |
|---|---|
| Arabic - United Arab Emirates |
| Arabic - Bahrain |
| Arabic - Algeria |
| Arabic - Egypt |
| Arabic - India |
| Arabic - Iraq |
| Arabic - Jordan |
| Arabic - Kuwait |
| Arabic - Lebanon |
| Arabic - Libya |
| Arabic - Morocco |
| Arabic - Oman |
| Arabic - Qatar |
| Arabic - Saudi Arabia |
| Arabic - Sudan |
| Arabic - Syria |
| Arabic - Tunisia |
| Arabic -Yemen |
| Belarusian - Belarus |
| Bulgarian - Bulgaria |
| Catalan - Spain |
| Czech - Czech Republic |
| Danish - Denmark |
| German - Austria |
| German - Belgium |
| German - Switzerland |
| German - Germany |
| German - Luxembourg |
| Greek - Greece |
| English - Australia |
| English - Canada |
| English - United Kingdom |
| English - India |
| English - New Zealand |
| English - Philippines |
| English - United States |
| English - South Africa |
| English - Zimbabwe |
| Spanish - Argentina |
| Spanish - Bolivia |
| Spanish - Chile |
| Spanish - Colombia |
| Spanish - Costa Rica |
| Spanish - Dominican Republic |
| Spanish - Ecuador |
| Spanish - Spain |
| Spanish - Guatemala |
| Spanish - Honduras |
| Spanish - Mexico |
| Spanish - Nicaragua |
| Spanish - Panama |
| Spanish - Peru |
| Spanish - Puerto Rico |
| Spanish - Paraguay |
| Spanish - El Salvador |
| Spanish - United States |
| Spanish - Uruguay |
| Spanish - Venezuela |
| Estonian - Estonia |
| Basque - Spain |
| FInnish - Finland |
| Faroese - Faroe Islands |
| French - Belgium |
| French - Canada |
| French - Switzerland |
| French - France |
| French - Luxembourg |
| Galician - Spain |
| Gujarati - India |
| Hebrew - Israel |
| Hindi - India |
| Croatian - Croatia |
| Hungarian _ Hungary |
| Indonesian - Indonesia |
| Icelandic - Iceland |
| Italian - Switzerland |
| Italian - Italy |
| Japanese - Japan |
| Korean - Republic of Korea |
| Lithuanian - Lithuania |
| Latvian - Latvia |
| Macedonian - North Macedonia |
| Mongolia - Mongolian |
| Malay - Malaysia |
| Norwegian(Bokmål) - Norway |
| Dutch - Belgium |
| Dutch - The Netherlands |
| Norwegian - Norway |
| Polish - Poland |
| Portuguese - Brazil |
| Portuguese - Portugal |
| Romansh - Switzerland |
| Romanian - Romania |
| Russian - Russia |
| Russian - Ukraine |
| Slovak - Slovakia |
| Slovenian - Slovenia |
| Albanian - Albania |
| Serbian - Serbia |
| Swedish - Finland |
| Swedish - Sweden |
| Tamil - India |
| Telugu - India |
| Thai - Thailand |
| Turkish - Turkey |
| Ukrainian - Ukraine |
| Urdu - Pakistan |
| Vietnamese - Vietnam |
| Chinese - China |
| Chinese - Hong Kong |
| Chinese -Taiwan |
