Mysql FORMAT - How to Format Numbers in MySQL

In MySQL, you can use the FORMAT() function to format a number to a specific format. This function accepts three parameters; the number, the number of decimal places, and an optional locale.

Syntax

The syntax goes like this:

FORMAT(X,D[,locale])

Where X is the number you want to format, D is the number of decimal places you’d like it rounded to, and locale is an optional argument that you can use to specify the locale to use (which determines the result’s decimal point, thousands separator, and grouping between separators).

Example

Here’s an example:

SELECT FORMAT(1, 3);

Result:

1.000

In this example, I specified that there should be 3 decimal places.

If you want to remove the fractional part from a number, you can specify 0 as the decimal place argument.

Here are some more examples:

SELECT 
    FORMAT(12345.6789, 0) AS 'Example 1',
    FORMAT(12345.6789, 2) AS 'Example 2',
    FORMAT(12345.6789, 5) AS 'Example 3';

Result:

+-----------+-----------+--------------+
| Example 1 | Example 2 | Example 3    |
+-----------+-----------+--------------+
| 12,346    | 12,345.68 | 12,345.67890 |
+-----------+-----------+--------------+

Specifying a Locale

You can also provide an optional third argument, which specifies the locale to use for the number.

SELECT 
 FORMAT(12345.6789, 2, 'en_NZ') AS 'Example 1',
 FORMAT(12345.6789, 2, 'de_DE') AS 'Example 2';

Result:

+-----------+-----------+
| Example 1 | Example 2 |
+-----------+-----------+
| 12,345.68 | 12.345,68 |
+-----------+-----------+

Here’s a list of locales supported my MySQL:

Locale Value	Meaning
ar_AE: Arabic – United Arab Emirates	ar_BH: Arabic – Bahrain
ar_DZ: Arabic – Algeria	ar_EG: Arabic – Egypt
ar_IN: Arabic – India	ar_IQ: Arabic – Iraq
ar_JO: Arabic – Jordan	ar_KW: Arabic – Kuwait
ar_LB: Arabic – Lebanon	ar_LY: Arabic – Libya
ar_MA: Arabic – Morocco	ar_OM: Arabic – Oman
ar_QA: Arabic – Qatar	ar_SA: Arabic – Saudi Arabia
ar_SD: Arabic – Sudan	ar_SY: Arabic – Syria
ar_TN: Arabic – Tunisia	ar_YE: Arabic – Yemen
be_BY: Belarusian – Belarus	bg_BG: Bulgarian – Bulgaria
ca_ES: Catalan – Spain	cs_CZ: Czech – Czech Republic
da_DK: Danish – Denmark	de_AT: German – Austria
de_BE: German – Belgium	de_CH: German – Switzerland
de_DE: German – Germany	de_LU: German – Luxembourg
el_GR: Greek – Greece	en_AU: English – Australia
en_CA: English – Canada	en_GB: English – United Kingdom
en_IN: English – India	en_NZ: English – New Zealand
en_PH: English – Philippines	en_US: English – United States
en_ZA: English – South Africa	en_ZW: English – Zimbabwe
es_AR: Spanish – Argentina	es_BO: Spanish – Bolivia
es_CL: Spanish – Chile	es_CO: Spanish – Colombia
es_CR: Spanish – Costa Rica	es_DO: Spanish – Dominican Republic
es_EC: Spanish – Ecuador	es_ES: Spanish – Spain
es_GT: Spanish – Guatemala	es_HN: Spanish – Honduras
es_MX: Spanish – Mexico	es_NI: Spanish – Nicaragua
es_PA: Spanish – Panama	es_PE: Spanish – Peru
es_PR: Spanish – Puerto Rico	es_PY: Spanish – Paraguay
es_SV: Spanish – El Salvador	es_US: Spanish – United States
es_UY: Spanish – Uruguay	es_VE: Spanish – Venezuela
et_EE: Estonian – Estonia	eu_ES: Basque – Basque
fi_FI: Finnish – Finland	fo_FO: Faroese – Faroe Islands
fr_BE: French – Belgium	fr_CA: French – Canada
fr_CH: French – Switzerland	fr_FR: French – France
fr_LU: French – Luxembourg	gl_ES: Galician – Spain
gu_IN: Gujarati – India	he_IL: Hebrew – Israel
hi_IN: Hindi – India	hr_HR: Croatian – Croatia
hu_HU: Hungarian – Hungary	id_ID: Indonesian – Indonesia
is_IS: Icelandic – Iceland	it_CH: Italian – Switzerland
it_IT: Italian – Italy	ja_JP: Japanese – Japan
ko_KR: Korean – Republic of Korea	lt_LT: Lithuanian – Lithuania
lv_LV: Latvian – Latvia	mk_MK: Macedonian – FYROM
mn_MN: Mongolia – Mongolian	ms_MY: Malay – Malaysia
nb_NO: Norwegian(Bokmål) – Norway	nl_BE: Dutch – Belgium
nl_NL: Dutch – The Netherlands	no_NO: Norwegian – Norway
pl_PL: Polish – Poland	pt_BR: Portugese – Brazil
pt_PT: Portugese – Portugal	rm_CH: Romansh – Switzerland
ro_RO: Romanian – Romania	ru_RU: Russian – Russia
ru_UA: Russian – Ukraine	sk_SK: Slovak – Slovakia
sl_SI: Slovenian – Slovenia	sq_AL: Albanian – Albania
sr_RS: Serbian – Yugoslavia	sv_FI: Swedish – Finland
sv_SE: Swedish – Sweden	ta_IN: Tamil – India
te_IN: Telugu – India	th_TH: Thai – Thailand
tr_TR: Turkish – Turkey	uk_UA: Ukrainian – Ukraine
ur_PK: Urdu – Pakistan	vi_VN: Vietnamese – Viet Nam
zh_CN: Chinese – China	zh_HK: Chinese – Hong Kong
zh_TW: Chinese – Taiwan Province of China