search
Search
Publish
menu
menu search toc more_vert
Robocat
Guest 0reps
Thanks for the thanks!
close
Comments
Log in or sign up
Cancel
Post
account_circle
Profile
exit_to_app
Sign out
help Ask a question
Share on Twitter
search
keyboard_voice
close
Searching Tips
Search for a recipe: "Creating a table in MySQL"
Search for an API documentation: "@append"
Search for code: "!dataframe"
Apply a tag filter: "#python"
Useful Shortcuts
/ to open search panel
Esc to close search panel
to navigate between search results
d to clear all current filters
Enter to expand content preview
icon_star
Doc Search
icon_star
Code Search Beta
SORRY NOTHING FOUND!
mic
Start speaking...
Voice search is only supported in Safari and Chrome.
Navigate to
A
A
share
thumb_up_alt
bookmark
arrow_backShare
Twitter
Facebook

MySQL | FORMAT method

Database
chevron_right
MySQL
chevron_right
Documentation
chevron_right
Functions
chevron_right
String
schedule Mar 10, 2022
Last updated
local_offer MySQL
Tags

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

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_UK

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 - Spain

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 - North Macedonia

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

Portuguese - Brazil

pt_PT

Portuguese - 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 - Serbia

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 - Vietnam

zh_CN

Chinese - China

zh_HK

Chinese - Hong Kong

zh_TW

Chinese -Taiwan

robocat
Published by Arthur Yanagisawa
Edited by 0 others
Did you find this page useful?
thumb_up
thumb_down
Ask a question or leave a feedback...