Different Ways to Format Currency Output in SQL

By:   |   Updated: 2021-11-11   |   Comments   |   Related: More > Functions System


Free SQL Server Performance and Monitoring Report


Dear Database Professional,

Download your free copy of the MSSQLTips.com SQL Server Performance and Monitoring Report. This survey was conducted in 2022 and polled 588 database professionals about various aspects of tuning and optimizing SQL Server.

Click here to download the free report

Problem

When working with currency formats, there are many different formats used around the world. For example, in the USA we use the $ for the currency symbol and a decimal point as a separator. Whereas in Latin America, we use the decimal point instead of a comma as the separator. Is there a simple way to handle the format of different currencies in Microsoft SQL Server?

Solution

In this tutorial, we will show different ways to output different currency formats with the T-SQL language.

FORMAT Function to Format Currency

You can change the currency format using the SQL Server FORMAT function. This function allows you to format currency output. The following example will show how to work with a different number of decimal places:

Number Format Syntax Output
Default currency value SELECT FORMAT(200.3639, 'C') AS 'Currency Format' $200.36
Currency, 3 decimals SELECT FORMAT(200.3639, 'C3') AS 'Currency Format' $200.364
Currency, 4 decimals SELECT FORMAT(200.3639, 'C4', 'en-us') AS 'Currency Format' $200.3639

Custom Format Currency Options

Here are other ways to have custom currency output. In these examples we use different SQL Server functions to change the output.

Number Format Syntax Output
Use REPLACE to change currency symbol SELECT REPLACE(FORMAT(200.36, 'C'),'$','£') AS 'Currency Format' £200.36
Custom format with spaces SELECT FORMAT(200.3639,'$### . ####') AS 'Currency Format';  $200 . 3639
Change period to comma SELECT REPLACE(FORMAT(200.36, 'C', 'en-us'),'.',',') AS 'Currency Format' $200,36

SQL FORMAT Function to Format Currency with Culture

There are hundreds of cultures already defined for SQL databases and the below output shows how the FORMAT function can be used and what the output looks like.

Language Syntax Output
Afrikaans - South Africa SELECT FORMAT (200.364, 'c', 'af-ZA') as date R200,36
Albanian - Albania SELECT FORMAT (200.364, 'c', 'sq-AL') as date 200 Lekë
Alsatian - France SELECT FORMAT (200.364, 'c', 'gsw-FR') as date 200,36 €
Amharic - Ethiopia SELECT FORMAT (200.364, 'c', 'am-ET') as date ብር200.36
Arabic - Algeria SELECT FORMAT (200.364, 'c', 'ar-DZ') as date 200.36 د.ج.‏
Arabic - Bahrain SELECT FORMAT (200.364, 'c', 'ar-BH') as date 200.364 د.ب.‏
Arabic - Egypt SELECT FORMAT (200.364, 'c', 'ar-EG') as date 200.36 ج.م.‏
Arabic - Iraq SELECT FORMAT (200.364, 'c', 'ar-IQ') as date 200.36 د.ع.‏
Arabic - Jordan SELECT FORMAT (200.364, 'c', 'ar-JO') as date 200.364 د.ا.‏
Arabic - Kuwait SELECT FORMAT (200.364, 'c', 'ar-KW') as date 200.364 د.ك.‏
Arabic - Lebanon SELECT FORMAT (200.364, 'c', 'ar-LB') as date 200.36 ل.ل.‏
Arabic - Libya SELECT FORMAT (200.364, 'c', 'ar-LY') as date 200.364 د.ل.‏
Arabic - Morocco SELECT FORMAT (200.364, 'c', 'ar-MA') as date 200.36 د.م.‏
Arabic - Oman SELECT FORMAT (200.364, 'c', 'ar-OM') as date 200.364 ر.ع.‏
Arabic - Qatar SELECT FORMAT (200.364, 'c', 'ar-QA') as date 200.36 ر.ق.‏
Arabic - Saudi Arabia SELECT FORMAT (200.364, 'c', 'ar-SA') as date 200.36 ر.س.‏
Arabic - Syria SELECT FORMAT (200.364, 'c', 'ar-SY') as date 200.36 ل.س.‏
Arabic - Tunisia SELECT FORMAT (200.364, 'c', 'ar-TN') as date 200.364 د.ت.‏
Arabic - U.A.E. SELECT FORMAT (200.364, 'c', 'ar-AE') as date 200.36 د.إ.‏
Arabic - Yemen SELECT FORMAT (200.364, 'c', 'ar-YE') as date 200.36 ر.ي.‏
Armenian - Armenia SELECT FORMAT (200.364, 'c', 'hy-AM') as date 200.36 ֏
Assamese - India SELECT FORMAT (200.364, 'c', 'as-IN') as date ₹ 200.36
Azerbaijani - Azerbaijan (Cyrillic) SELECT FORMAT (200.364, 'c', 'az-Cyrl-AZ') as date 200,36 ₼
Azerbaijani - Azerbaijan (Latin) SELECT FORMAT (200.364, 'c', 'az-Latn-AZ') as date 200,36 ₼
Bangla (Bangladesh) SELECT FORMAT (200.364, 'c', 'bn-BD') as date 200.36
Bangla - India (Bengali Script) SELECT FORMAT (200.364, 'c', 'bn-IN') as date ₹ 200.36
Bashkir - Russia SELECT FORMAT (200.364, 'c', 'ba-RU') as date 200,36 ₽
Basque - Basque SELECT FORMAT (200.364, 'c', 'eu-ES') as date 200,36 €
Belarusian - Belarus SELECT FORMAT (200.364, 'c', 'be-BY') as date 200,36 Br
Bosnian - Bosnia and Herzegovina (Cyrillic) SELECT FORMAT (200.364, 'c', 'bs-Cyrl-BA') as date 200,36 КМ
Bosnian - Bosnia and Herzegovina (Latin) SELECT FORMAT (200.364, 'c', 'bs-Latn-BA') as date 200,36 KM
Breton - France SELECT FORMAT (200.364, 'c', 'br-FR') as date 200,36 €
Bulgarian - Bulgaria SELECT FORMAT (200.364, 'c', 'bg-BG') as date 200,36 лв.
Burmese - Myanmar SELECT FORMAT (200.364, 'c', 'my-MM') as date 200 K
Catalan - Catalan SELECT FORMAT (200.364, 'c', 'ca-ES') as date 200,36 €
Central Atlas Tamazight (Latin) - Algeria SELECT FORMAT (200.364, 'c', 'fr-FR') as date 200,36 €
Central Atlas Tamazight (Latin) - Algeria SELECT FORMAT (200.364, 'c', 'tzm-Latn-DZ') as date 200,36 DA
Central Atlas Tamazight (Tifinagh) - Morocco SELECT FORMAT (200.364, 'c', 'tzm-Tfng-MA') as date 200,36 ⴷⵔ
Central Kurdish (Iraq) SELECT FORMAT (200.364, 'c', 'ku-Arab-IQ') as date د.ع.‏200.36
Cherokee (Cherokee, United States) SELECT FORMAT (200.364, 'c', 'chr-Cher-US') as date $200.36
Chinese (Simplified, China) SELECT FORMAT (200.364, 'c', 'zh-CN') as date ¥200.36
Chinese - (Traditional, Taiwan) SELECT FORMAT (200.364, 'c', 'zh-TW') as date NT$200.36
Corsican - France SELECT FORMAT (200.364, 'c', 'co-FR') as date 200,36 €
Croatian - Bosnia and Herzegovina SELECT FORMAT (200.364, 'c', 'hr-BA') as date 200,36 KM
Croatian - Croatia SELECT FORMAT (200.364, 'c', 'hr-HR') as date 200,36 kn
Czech - Czech Republic SELECT FORMAT (200.364, 'c', 'cs-CZ') as date 200,36 Kč
Danish - Denmark SELECT FORMAT (200.364, 'c', 'da-DK') as date 200,36 kr.
Dari - Afghanistan SELECT FORMAT (200.364, 'c', 'prs-AF') as date ؋200.36
Divehi - Maldives SELECT FORMAT (200.364, 'c', 'dv-MV') as date 200.36 ރ.
Dutch - Belgium SELECT FORMAT (200.364, 'c', 'nl-BE') as date € 200,36
Dutch - Netherlands SELECT FORMAT (200.364, 'c', 'nl-NL') as date € 200,36
Dzongkha SELECT FORMAT (200.364, 'c', 'dz-BT') as date Nu.200.36
English - Australia SELECT FORMAT (200.364, 'c', 'en-AU') as date $200.36
English - Belize SELECT FORMAT (200.364, 'c', 'en-BZ') as date $200.36
English - Canada SELECT FORMAT (200.364, 'c', 'en-CA') as date $200.36
English - Caribbean SELECT FORMAT (200.364, 'c', 'en-029') as date EC$200.36
English - India SELECT FORMAT (200.364, 'c', 'en-IN') as date ₹ 200.36
English - Ireland SELECT FORMAT (200.364, 'c', 'en-IE') as date €200.36
English - Jamaica SELECT FORMAT (200.364, 'c', 'en-JM') as date $200.36
English - Malaysia SELECT FORMAT (200.364, 'c', 'en-MY') as date RM200.36
English - New Zealand SELECT FORMAT (200.364, 'c', 'en-NZ') as date $200.36
English - Philippines SELECT FORMAT (200.364, 'c', 'en-PH') as date ₱200.36
English - Singapore SELECT FORMAT (200.364, 'c', 'en-SG') as date $200.36
English - South Africa SELECT FORMAT (200.364, 'c', 'en-ZA') as date R200,36
English - Trinidad SELECT FORMAT (200.364, 'c', 'en-TT') as date $200.36
English - Great Britain SELECT FORMAT (200.364, 'c', 'en-GB') as date £200.36
English - United States SELECT FORMAT (200.364, 'c', 'en-US') as date $200.36
English - Zimbabwe SELECT FORMAT (200.364, 'c', 'en-ZW') as date US$200.36
Estonian - Estonia SELECT FORMAT (200.364, 'c', 'et-EE') as date 200,36 €
Faroese - Faroe Islands SELECT FORMAT (200.364, 'c', 'fo-FO') as date 200,36 kr
Filipino - Philippines SELECT FORMAT (200.364, 'c', 'fil-PH') as date ₱200.36
Finnish - Finland SELECT FORMAT (200.364, 'c', 'fi-FI') as date 200,36 €
French - Belgium SELECT FORMAT (200.364, 'c', 'fr-BE') as date 200,36 €
French - Canada SELECT FORMAT (200.364, 'c', 'fr-CA') as date 200,36 $
French - France SELECT FORMAT (200.364, 'c', 'fr-FR') as date 200,36 €
French - Luxembourg SELECT FORMAT (200.364, 'c', 'fr-LU') as date 200,36 €
French - Monaco SELECT FORMAT (200.364, 'c', 'fr-MC') as date 200,36 €
French - Switzerland SELECT FORMAT (200.364, 'c', 'fr-CH') as date 200.36 CHF
Frisian - Netherlands SELECT FORMAT (200.364, 'c', 'fy-NL') as date € 200,36
Fulah (Latin, Senegal) SELECT FORMAT (200.364, 'c', 'ff-Latn-SN') as date 200,36 CFA
Galician - Galician SELECT FORMAT (200.364, 'c', 'gl-ES') as date 200,36 €
Georgian - Georgia SELECT FORMAT (200.364, 'c', 'ka-GE') as date 200,36 ₾
German - Austria SELECT FORMAT (200.364, 'c', 'de-AT') as date € 200,36
German - Germany SELECT FORMAT (200.364, 'c', 'de-DE') as date 200,36 €
German - Liechtenstein SELECT FORMAT (200.364, 'c', 'de-LI') as date CHF 200.36
German - Luxembourg SELECT FORMAT (200.364, 'c', 'de-LU') as date 200,36 €
German - Switzerland SELECT FORMAT (200.364, 'c', 'de-CH') as date CHF 200.36
Greek - Greece SELECT FORMAT (200.364, 'c', 'el-GR') as date 200,36 €
Greenlandic - Greenland SELECT FORMAT (200.364, 'c', 'kl-GL') as date kr.200,36
Guarani - Paraguay SELECT FORMAT (200.364, 'c', 'gn-PY') as date 200 ₲
Gujarati - India (Gujarati Script) SELECT FORMAT (200.364, 'c', 'gu-IN') as date ₹200.36
Hausa (Latin) - Nigeria SELECT FORMAT (200.364, 'c', 'ha-Latn-NG') as date ₦ 200.36
Hawaiian - United States SELECT FORMAT (200.364, 'c', 'haw-US') as date $200.36
Hebrew - Israel SELECT FORMAT (200.364, 'c', 'he-IL') as date ₪ 200.36
Hindi - India SELECT FORMAT (200.364, 'c', 'hi-IN') as date ₹200.36
Hungarian - Hungary SELECT FORMAT (200.364, 'c', 'hu-HU') as date 200,36 Ft
Icelandic - Iceland SELECT FORMAT (200.364, 'c', 'is-IS') as date 200 kr
Igbo - Nigeria SELECT FORMAT (200.364, 'c', 'ig-NG') as date ₦200.36
Inari Sami - Finland SELECT FORMAT (200.364, 'c', 'smn-FI') as date 200,36 €
Indonesian - Indonesia SELECT FORMAT (200.364, 'c', 'id-ID') as date Rp200,36
Inuktitut (Latin) - Canada SELECT FORMAT (200.364, 'c', 'iu-Latn-CA') as date $200.36
Inuktitut (Syllabics) - Canada SELECT FORMAT (200.364, 'c', 'iu-Cans-CA') as date $200.36
Irish - Ireland SELECT FORMAT (200.364, 'c', 'ga-IE') as date €200.36
isiXhosa / Xhosa - South Africa SELECT FORMAT (200.364, 'c', 'xh-ZA') as date R200.36
isiZulu / Zulu - South Africa SELECT FORMAT (200.364, 'c', 'zu-ZA') as date R200.36
Italian - Italy SELECT FORMAT (200.364, 'c', 'it-IT') as date 200,36 €
Italian - Switzerland SELECT FORMAT (200.364, 'c', 'it-CH') as date CHF 200.36
Japanese - Japan SELECT FORMAT (200.364, 'c', 'ja-JP') as date ¥200
Javanese (Latin) - Indonesia SELECT FORMAT (200.364, 'c', 'jv-Latn-ID') as date Rp200
Kannada - India (Kannada Script) SELECT FORMAT (200.364, 'c', 'kn-IN') as date ₹200.36
Kazakh - Kazakhstan SELECT FORMAT (200.364, 'c', 'kk-KZ') as date 200,36 ₸
Khmer - Cambodia SELECT FORMAT (200.364, 'c', 'km-KH') as date 200.36
K'iche - Guatemala SELECT FORMAT (200.364, 'c', 'qut-GT') as date Q200.36
Kinyarwanda - Rwanda SELECT FORMAT (200.364, 'c', 'rw-RW') as date RF 200
Konkani - India SELECT FORMAT (200.364, 'c', 'kok-IN') as date ₹ 200.36
Korean(Extended Wansung) - Korea SELECT FORMAT (200.364, 'c', 'ko-KR') as date ₩200
Kyrgyz - Kyrgyzstan SELECT FORMAT (200.364, 'c', 'ky-KG') as date 200,36 сом
Lao - Lao PDR SELECT FORMAT (200.364, 'c', 'lo-LA') as date ₭200
Latvian - Legacy SELECT FORMAT (200.364, 'c', 'lv-LV') as date 200,36 €
Latvian - Standard SELECT FORMAT (200.364, 'c', 'lv-LV') as date 200,36 €
Lithuanian - Lithuania SELECT FORMAT (200.364, 'c', 'lt-LT') as date 200,36 €
Lower Sorbian - Germany SELECT FORMAT (200.364, 'c', 'dsb-DE') as date 200,36 €
Lule Sami - Norway SELECT FORMAT (200.364, 'c', 'smj-NO') as date kr 200,36
Lule Sami - Sweden SELECT FORMAT (200.364, 'c', 'smj-SE') as date 200,36 kr
Luxembourgish - Luxembourg SELECT FORMAT (200.364, 'c', 'lb-LU') as date 200,36 €
Macedonian - F.Y.R.O.M SELECT FORMAT (200.364, 'c', 'mk-MK') as date 200,36 ден
Malay - Brunei SELECT FORMAT (200.364, 'c', 'ms-BN') as date $ 200,36
Malay - Malaysia SELECT FORMAT (200.364, 'c', 'ms-MY') as date RM200.36
Malayalam - India (Malayalam Script) SELECT FORMAT (200.364, 'c', 'ml-IN') as date ₹200.36
Maltese - Malta SELECT FORMAT (200.364, 'c', 'mt-MT') as date €200.36
Maori - New Zealand SELECT FORMAT (200.364, 'c', 'mi-NZ') as date $200.36
Mapudungun - Chile SELECT FORMAT (200.364, 'c', 'arn-CL') as date $ 200,36
Marathi - India SELECT FORMAT (200.364, 'c', 'mr-IN') as date ₹200`36
Mohawk - Mohawk SELECT FORMAT (200.364, 'c', 'moh-CA') as date $200.36
Mongolian (Cyrillic) - Mongolia SELECT FORMAT (200.364, 'c', 'mn-MN') as date ₮ 200.36
Mongolian (Mongolian) - Mongolia SELECT FORMAT (200.364, 'c', 'mn-Mong-MN') as date ₮200.36
Mongolian (Traditional Mongolian – China – Legacy) SELECT FORMAT (200.364, 'c', 'mn-Mong-CN') as date ¥200.36
Mongolian (Traditional Mongolian – China – Standard) SELECT FORMAT (200.364, 'c', 'mn-Mong-CN') as date ¥200.36
N'ko – Guinea SELECT FORMAT (200.364, 'c', 'nqo-GN') as date ߖߕ. 200.36
Nepali - Federal Democratic Republic of Nepal SELECT FORMAT (200.364, 'c', 'ne-NP') as date रु 200.36
Northern Sami - Finland SELECT FORMAT (200.364, 'c', 'se-FI') as date 200,36 €
Northern Sami - Norway SELECT FORMAT (200.364, 'c', 'se-NO') as date 200,36 kr
Northern Sami - Sweden SELECT FORMAT (200.364, 'c', 'se-SE') as date 200,36 kr
Norwegian - Norway (Bokmål) SELECT FORMAT (200.364, 'c', 'nb-NO') as date kr 200,36
Norwegian - Norway (Nynorsk) SELECT FORMAT (200.364, 'c', 'nn-NO') as date 200,36 kr
Occitan - France SELECT FORMAT (200.364, 'c', 'oc-FR') as date 200,36 €
Odia - India (Odia Script) SELECT FORMAT (200.364, 'c', 'or-IN') as date ₹ 200.36
Pashto - Afghanistan SELECT FORMAT (200.364, 'c', 'ps-AF') as date 200 ؋
Persian SELECT FORMAT (200.364, 'c', 'fa-IR') as date 200/36ريال
Polish - Poland SELECT FORMAT (200.364, 'c', 'pl-PL') as date 200,36 zł
Portuguese - Brazil SELECT FORMAT (200.364, 'c', 'pt-BR') as date R$ 200,36
Portuguese - Portugal SELECT FORMAT (200.364, 'c', 'pt-PT') as date 200,36 €
Punjabi - India (Gurmukhi Script) SELECT FORMAT (200.364, 'c', 'pa-IN') as date ₹ 200.36
Punjabi (Islamic Republic of Pakistan) SELECT FORMAT (200.364, 'c', 'pa-Arab-PK') as date Rs 200.36
Quechua - Bolivia SELECT FORMAT (200.364, 'c', 'quz-BO') as date Bs. 200,36
Quechua - Ecuador SELECT FORMAT (200.364, 'c', 'quz-EC') as date $ 200,36
Quechua - Peru SELECT FORMAT (200.364, 'c', 'quz-PE') as date S/ 200.36
Romanian - Romania SELECT FORMAT (200.364, 'c', 'ro-RO') as date 200,36 lei
Romansh - Switzerland SELECT FORMAT (200.364, 'c', 'rm-CH') as date 200.36 CHF
Russian - Russia SELECT FORMAT (200.364, 'c', 'ru-RU') as date 200,36 ₽
Sakha - Russia SELECT FORMAT (200.364, 'c', 'sah-RU') as date 200,36 ₽
Sanskrit - India SELECT FORMAT (200.364, 'c', 'sa-IN') as date ₹ 200.36
Scottish Gaelic - Great Britain SELECT FORMAT (200.364, 'c', 'gd-GB') as date £200.36
Serbian - Bosnia and Herzegovina (Cyrillic) SELECT FORMAT (200.364, 'c', 'sr-Cyrl-BA') as date 200,36 КМ
Serbian - Bosnia and Herzegovina (Latin) SELECT FORMAT (200.364, 'c', 'sr-Latn-BA') as date 200,36 KM
Serbian - Montenegro (Cyrillic) SELECT FORMAT (200.364, 'c', 'sr-Cyrl-ME') as date 200,36 €
Serbian - Montenegro (Latin) SELECT FORMAT (200.364, 'c', 'sr-Latn-ME') as date 200,36 €
Serbian - Serbia (Cyrillic) SELECT FORMAT (200.364, 'c', 'sr-Cyrl-RS') as date 200,36 дин.
Serbian - Serbia (Latin) SELECT FORMAT (200.364, 'c', 'sr-Latn-RS') as date 200 RSD
Serbian - Serbia and Montenegro (Former) (Cyrillic) SELECT FORMAT (200.364, 'c', 'sr-Cyrl-CS') as date 200,36 дин.
Serbian - Serbia and Montenegro (Former) (Latin) SELECT FORMAT (200.364, 'c', 'sr-Latn-CS') as date 200,36 din.
Sesotho sa Leboa / Northern Sotho - South Africa SELECT FORMAT (200.364, 'c', 'nso-ZA') as date R 200.36
Setswana / Tswana - Botswana SELECT FORMAT (200.364, 'c', 'tn-BW') as date P200.36
Setswana / Tswana - South Africa SELECT FORMAT (200.364, 'c', 'tn-ZA') as date R200.36
Shona – Zimbabwe SELECT FORMAT (200.364, 'c', 'sn-Latn-ZW') as date $200.36
Sindhi (Islamic Republic of Pakistan) SELECT FORMAT (200.364, 'c', 'sd-Arab-PK') as date Rs 200.36
Sinhala - Sri Lanka SELECT FORMAT (200.364, 'c', 'si-LK') as date රු.200.36
Skolt Sami - Finland SELECT FORMAT (200.364, 'c', 'sms-FI') as date 200,36 €
Slovak - Slovakia SELECT FORMAT (200.364, 'c', 'sk-SK') as date 200,36 €
Slovenian - Slovenia SELECT FORMAT (200.364, 'c', 'sl-SI') as date 200,36 €
Southern Sami - Norway SELECT FORMAT (200.364, 'c', 'sma-NO') as date kr 200,36
Southern Sami - Sweden SELECT FORMAT (200.364, 'c', 'sma-SE') as date 200,36 kr
Spanish - Argentina SELECT FORMAT (200.364, 'c', 'es-AR') as date $ 200,36
Spanish - Bolivarian Republic of Venezuela SELECT FORMAT (200.364, 'c', 'es-VE') as date Bs.S200,36
Spanish - Bolivia SELECT FORMAT (200.364, 'c', 'es-BO') as date Bs200,36
Spanish - Chile SELECT FORMAT (200.364, 'c', 'es-CL') as date $200
Spanish - Colombia SELECT FORMAT (200.364, 'c', 'es-CO') as date $ 200,36
Spanish - Costa Rica SELECT FORMAT (200.364, 'c', 'es-CR') as date ₡200,36
Spanish - Dominican Republic SELECT FORMAT (200.364, 'c', 'es-DO') as date $200.36
Spanish - Ecuador SELECT FORMAT (200.364, 'c', 'es-EC') as date $200,36
Spanish - El Salvador SELECT FORMAT (200.364, 'c', 'es-SV') as date $200.36
Spanish - Guatemala SELECT FORMAT (200.364, 'c', 'es-GT') as date Q200.36
Spanish - Honduras SELECT FORMAT (200.364, 'c', 'es-HN') as date L200.36
Spanish - Latin America SELECT FORMAT (200.364, 'c', 'es-419') as date XDR200.36
Spanish - Mexico SELECT FORMAT (200.364, 'c', 'es-MX') as date $200.36
Spanish - Nicaragua SELECT FORMAT (200.364, 'c', 'es-NI') as date C$200.36
Spanish - Panama SELECT FORMAT (200.364, 'c', 'es-PA') as date B/.200.36
Spanish - Paraguay SELECT FORMAT (200.364, 'c', 'es-PY') as date ₲ 200
Spanish - Peru SELECT FORMAT (200.364, 'c', 'es-PE') as date S/ 200.36
Spanish - Commonwealth of Puerto Rico SELECT FORMAT (200.364, 'c', 'es-PR') as date $200.36
Spanish - Spain (International Sort) SELECT FORMAT (200.364, 'c', 'es-ES') as date 200,36 €
Spanish - Spain (Traditional Sort) SELECT FORMAT (200.364, 'c', 'es-ES_tradnl') as date 200,36 €
Spanish - United States SELECT FORMAT (200.364, 'c', 'es-US') as date $200.36
Spanish - Uruguay SELECT FORMAT (200.364, 'c', 'es-UY') as date $ 200,36
Standard Moroccan Tamazight - Morocco SELECT FORMAT (200.364, 'c', 'zgh-Tfng-MA') as date 200,36MAD
Swahili - Kenya SELECT FORMAT (200.364, 'c', 'sw-KE') as date Ksh 200.36
Swedish - Finland SELECT FORMAT (200.364, 'c', 'sv-FI') as date 200,36 €
Swedish - Sweden SELECT FORMAT (200.364, 'c', 'sv-SE') as date 200,36 kr
Syriac - Syria SELECT FORMAT (200.364, 'c', 'syr-SY') as date 200.36 ܠ.ܣ.‏
Tajik - Tajikistan SELECT FORMAT (200.364, 'c', 'tg-Cyrl-TJ') as date 200,36 смн
Tamil - India SELECT FORMAT (200.364, 'c', 'ta-IN') as date ₹ 200.36
Tamil - Sri Lanka SELECT FORMAT (200.364, 'c', 'ta-LK') as date Rs. 200.36
Tatar – Russia (Legacy) SELECT FORMAT (200.364, 'c', 'tt-RU') as date 200,36 ₽
Tatar – Russia (Standard) SELECT FORMAT (200.364, 'c', 'tt-RU') as date 200,36 ₽
Telugu - India (Telugu Script) SELECT FORMAT (200.364, 'c', 'te-IN') as date ₹200.36
Thai - Thailand SELECT FORMAT (200.364, 'c', 'th-TH') as date ฿200.36
Tibetan - China SELECT FORMAT (200.364, 'c', 'bo-CN') as date ¥200.36
Tigrinya (Eritrea) SELECT FORMAT (200.364, 'c', 'ti-ET') as date Br200.36
Tigrinya (Ethiopia) SELECT FORMAT (200.364, 'c', 'ti-ET') as date Br200.36
Turkish - Turkey SELECT FORMAT (200.364, 'c', 'tr-TR') as date ₺200,36
Turkmen - Turkmenistan SELECT FORMAT (200.364, 'c', 'tk-TM') as date 200,36m.
Ukrainian - Ukraine SELECT FORMAT (200.364, 'c', 'uk-UA') as date 200,36 ₴
Upper Sorbian - Germany SELECT FORMAT (200.364, 'c', 'hsb-DE') as date 200,36 €
Urdu – India SELECT FORMAT (200.364, 'c', 'ur-IN') as date ₹200.36
Urdu (Islamic Republic of Pakistan) SELECT FORMAT (200.364, 'c', 'ur-PK') as date Rs 200.36
Uyghur - China SELECT FORMAT (200.364, 'c', 'ug-CN') as date ¥200.36
Uzbek - Uzbekistan (Cyrillic) SELECT FORMAT (200.364, 'c', 'uz-Cyrl-UZ') as date 200,36 сўм
Uzbek - Uzbekistan (Latin) SELECT FORMAT (200.364, 'c', 'uz-Latn-UZ') as date 200,36 soʻm
Valencian - Valencia SELECT FORMAT (200.364, 'c', 'ca-ES-valencia') as date 200,36 €
Vietnamese - Vietnam SELECT FORMAT (200.364, 'c', 'vi-VN') as date 200,36 ₫
Welsh - Great Britain SELECT FORMAT (200.364, 'c', 'cy-GB') as date £200.36
Wolof - Senegal SELECT FORMAT (200.364, 'c', 'wo-SN') as date 200,36 CFA
Yi - China SELECT FORMAT (200.364, 'c', 'ii-CN') as date ¥200.36

Script to Recreate Currency Format List

The below script will create a temporary table to store the language and language code and then use this to display all of the currency formats for the given value.

CREATE TABLE #temp ([language] nvarchar(100), [langcode] nvarchar(20))
INSERT INTO #temp
VALUES('Afrikaans - South Africa','af-ZA')
,('Albanian - Albania','sq-AL')
,('Alsatian - France','gsw-FR')
,('Amharic - Ethiopia','am-ET')
,('Arabic - Algeria','ar-DZ')
,('Arabic - Bahrain','ar-BH')
,('Arabic - Egypt','ar-EG')
,('Arabic - Iraq','ar-IQ')
,('Arabic - Jordan','ar-JO')
,('Arabic - Kuwait','ar-KW')
,('Arabic - Lebanon','ar-LB')
,('Arabic - Libya','ar-LY')
,('Arabic - Morocco','ar-MA')
,('Arabic - Oman','ar-OM')
,('Arabic - Qatar','ar-QA')
,('Arabic - Saudi Arabia','ar-SA')
,('Arabic - Syria','ar-SY')
,('Arabic - Tunisia','ar-TN')
,('Arabic - U.A.E.','ar-AE')
,('Arabic - Yemen','ar-YE')
,('Armenian - Armenia','hy-AM')
,('Assamese - India','as-IN')
,('Azerbaijani - Azerbaijan (Cyrillic)','az-Cyrl-AZ')
,('Azerbaijani - Azerbaijan (Latin)','az-Latn-AZ')
,('Bangla (Bangladesh)','bn-BD')
,('Bangla - India (Bengali Script)','bn-IN')
,('Bashkir - Russia','ba-RU')
,('Basque - Basque','eu-ES')
,('Belarusian - Belarus','be-BY')
,('Bosnian - Bosnia and Herzegovina (Cyrillic)','bs-Cyrl-BA')
,('Bosnian - Bosnia and Herzegovina (Latin)','bs-Latn-BA')
,('Breton - France','br-FR')
,('Bulgarian - Bulgaria','bg-BG')
,('Burmese - Myanmar','my-MM')
,('Catalan - Catalan','ca-ES')
,('Central Atlas Tamazight (Latin) - Algeria','fr-FR')
,('Central Atlas Tamazight (Latin) - Algeria','tzm-Latn-DZ')
,('Central Atlas Tamazight (Tifinagh) - Morocco','tzm-Tfng-MA')
,('Central Kurdish (Iraq)','ku-Arab-IQ')
,('Cherokee (Cherokee, United States)','chr-Cher-US')
,('Chinese (Simplified, China)','zh-CN')
,('Chinese - (Traditional, Taiwan)','zh-TW')
,('Corsican - France','co-FR')
,('Croatian - Bosnia and Herzegovina','hr-BA')
,('Croatian - Croatia','hr-HR')
,('Czech - Czech Republic','cs-CZ')
,('Danish - Denmark','da-DK')
,('Dari - Afghanistan','prs-AF')
,('Divehi - Maldives','dv-MV')
,('Dutch - Belgium','nl-BE')
,('Dutch - Netherlands','nl-NL')
,('Dzongkha','dz-BT')
,('English - Australia','en-AU')
,('English - Belize','en-BZ')
,('English - Canada','en-CA')
,('English - Caribbean','en-029')
,('English - India','en-IN')
,('English - Ireland','en-IE')
,('English - Jamaica','en-JM')
,('English - Malaysia','en-MY')
,('English - New Zealand','en-NZ')
,('English - Philippines','en-PH')
,('English - Singapore','en-SG')
,('English - South Africa','en-ZA')
,('English - Trinidad','en-TT')
,('English - Great Britain','en-GB')
,('English - United States','en-US')
,('English - Zimbabwe','en-ZW')
,('Estonian - Estonia','et-EE')
,('Faroese - Faroe Islands','fo-FO')
,('Filipino - Philippines','fil-PH')
,('Finnish - Finland','fi-FI')
,('French - Belgium','fr-BE')
,('French - Canada','fr-CA')
,('French - France','fr-FR')
,('French - Luxembourg','fr-LU')
,('French - Monaco','fr-MC')
,('French - Switzerland','fr-CH')
,('Frisian - Netherlands','fy-NL')
,('Fulah (Latin, Senegal)','ff-Latn-SN')
,('Galician - Galician','gl-ES')
,('Georgian - Georgia','ka-GE')
,('German - Austria','de-AT')
,('German - Germany','de-DE')
,('German - Liechtenstein','de-LI')
,('German - Luxembourg','de-LU')
,('German - Switzerland','de-CH')
,('Greek - Greece','el-GR')
,('Greenlandic - Greenland','kl-GL')
,('Guarani - Paraguay','gn-PY')
,('Gujarati - India (Gujarati Script)','gu-IN')
,('Hausa (Latin) - Nigeria','ha-Latn-NG')
,('Hawaiian - United States','haw-US')
,('Hebrew - Israel','he-IL')
,('Hindi - India','hi-IN')
,('Hungarian - Hungary','hu-HU')
,('Icelandic - Iceland','is-IS')
,('Igbo - Nigeria','ig-NG')
,('Inari Sami - Finland','smn-FI')
,('Indonesian - Indonesia','id-ID')
,('Inuktitut (Latin) - Canada','iu-Latn-CA')
,('Inuktitut (Syllabics) - Canada','iu-Cans-CA')
,('Irish - Ireland','ga-IE')
,('isiXhosa / Xhosa - South Africa','xh-ZA')
,('isiZulu / Zulu - South Africa','zu-ZA')
,('Italian - Italy','it-IT')
,('Italian - Switzerland','it-CH')
,('Japanese - Japan','ja-JP')
,('Javanese (Latin) - Indonesia','jv-Latn-ID')
,('Kannada - India (Kannada Script)','kn-IN')
,('Kazakh - Kazakhstan','kk-KZ')
,('Khmer - Cambodia','km-KH')
,('K''iche - Guatemala','qut-GT')
,('Kinyarwanda - Rwanda','rw-RW')
,('Konkani - India','kok-IN')
,('Korean(Extended Wansung) - Korea','ko-KR')
,('Kyrgyz - Kyrgyzstan','ky-KG')
,('Lao - Lao PDR','lo-LA')
,('Latvian - Legacy','lv-LV')
,('Latvian - Standard','lv-LV')
,('Lithuanian - Lithuania','lt-LT')
,('Lower Sorbian - Germany','dsb-DE')
,('Lule Sami - Norway','smj-NO')
,('Lule Sami - Sweden','smj-SE')
,('Luxembourgish - Luxembourg','lb-LU')
,('Macedonian - F.Y.R.O.M','mk-MK')
,('Malay - Brunei','ms-BN')
,('Malay - Malaysia','ms-MY')
,('Malayalam - India (Malayalam Script)','ml-IN')
,('Maltese - Malta','mt-MT')
,('Maori - New Zealand','mi-NZ')
,('Mapudungun - Chile','arn-CL')
,('Marathi - India','mr-IN')
,('Mohawk - Mohawk','moh-CA')
,('Mongolian (Cyrillic) - Mongolia','mn-MN')
,('Mongolian (Mongolian) - Mongolia','mn-Mong-MN')
,('Mongolian (Traditional Mongolian – China – Legacy)','mn-Mong-CN')
,('Mongolian (Traditional Mongolian – China – Standard)','mn-Mong-CN')
,('N''ko – Guinea','nqo-GN')
,('Nepali - Federal Democratic Republic of Nepal','ne-NP')
,('Northern Sami - Finland','se-FI')
,('Northern Sami - Norway','se-NO')
,('Northern Sami - Sweden','se-SE')
,('Norwegian - Norway (Bokmål)','nb-NO')
,('Norwegian - Norway (Nynorsk)','nn-NO')
,('Occitan - France','oc-FR')
,('Odia - India (Odia Script)','or-IN')
,('Pashto - Afghanistan','ps-AF')
,('Persian','fa-IR')
,('Polish - Poland','pl-PL')
,('Portuguese - Brazil','pt-BR')
,('Portuguese - Portugal','pt-PT')
,('Punjabi - India (Gurmukhi Script)','pa-IN')
,('Punjabi (Islamic Republic of Pakistan)','pa-Arab-PK')
,('Quechua - Bolivia','quz-BO')
,('Quechua - Ecuador','quz-EC')
,('Quechua - Peru','quz-PE')
,('Romanian - Romania','ro-RO')
,('Romansh - Switzerland','rm-CH')
,('Russian - Russia','ru-RU')
,('Sakha - Russia','sah-RU')
,('Sanskrit - India','sa-IN')
,('Scottish Gaelic - Great Britain','gd-GB')
,('Serbian - Bosnia and Herzegovina (Cyrillic)','sr-Cyrl-BA')
,('Serbian - Bosnia and Herzegovina (Latin)','sr-Latn-BA')
,('Serbian - Montenegro (Cyrillic)','sr-Cyrl-ME')
,('Serbian - Montenegro (Latin)','sr-Latn-ME')
,('Serbian - Serbia (Cyrillic)','sr-Cyrl-RS')
,('Serbian - Serbia (Latin)','sr-Latn-RS')
,('Serbian - Serbia and Montenegro (Former) (Cyrillic)','sr-Cyrl-CS')
,('Serbian - Serbia and Montenegro (Former) (Latin)','sr-Latn-CS')
,('Sesotho sa Leboa / Northern Sotho - South Africa','nso-ZA')
,('Setswana / Tswana - Botswana','tn-BW')
,('Setswana / Tswana - South Africa','tn-ZA')
,('Shona – Zimbabwe','sn-Latn-ZW')
,('Sindhi (Islamic Republic of Pakistan)','sd-Arab-PK')
,('Sinhala - Sri Lanka','si-LK')
,('Skolt Sami - Finland','sms-FI')
,('Slovak - Slovakia','sk-SK')
,('Slovenian - Slovenia','sl-SI')
,('Southern Sami - Norway','sma-NO')
,('Southern Sami - Sweden','sma-SE')
,('Spanish - Argentina','es-AR')
,('Spanish - Bolivarian Republic of Venezuela','es-VE')
,('Spanish - Bolivia','es-BO')
,('Spanish - Chile','es-CL')
,('Spanish - Colombia','es-CO')
,('Spanish - Costa Rica','es-CR')
,('Spanish - Dominican Republic','es-DO')
,('Spanish - Ecuador','es-EC')
,('Spanish - El Salvador','es-SV')
,('Spanish - Guatemala','es-GT')
,('Spanish - Honduras','es-HN')
,('Spanish - Latin America','es-419')
,('Spanish - Mexico','es-MX')
,('Spanish - Nicaragua','es-NI')
,('Spanish - Panama','es-PA')
,('Spanish - Paraguay','es-PY')
,('Spanish - Peru','es-PE')
,('Spanish - Commonwealth of Puerto Rico','es-PR')
,('Spanish - Spain (International Sort)','es-ES')
,('Spanish - Spain (Traditional Sort)','es-ES_tradnl')
,('Spanish - United States','es-US')
,('Spanish - Uruguay','es-UY')
,('Standard Moroccan Tamazight - Morocco','zgh-Tfng-MA')
,('Swahili - Kenya','sw-KE')
,('Swedish - Finland','sv-FI')
,('Swedish - Sweden','sv-SE')
,('Syriac - Syria','syr-SY')
,('Tajik - Tajikistan','tg-Cyrl-TJ')
,('Tamil - India','ta-IN')
,('Tamil - Sri Lanka','ta-LK')
,('Tatar – Russia (Legacy)','tt-RU')
,('Tatar – Russia (Standard)','tt-RU')
,('Telugu - India (Telugu Script)','te-IN')
,('Thai - Thailand','th-TH')
,('Tibetan - China','bo-CN')
,('Tigrinya (Eritrea)','ti-ET')
,('Tigrinya (Ethiopia)','ti-ET')
,('Turkish - Turkey','tr-TR')
,('Turkmen - Turkmenistan','tk-TM')
,('Ukrainian - Ukraine','uk-UA')
,('Upper Sorbian - Germany','hsb-DE')
,('Urdu – India','ur-IN')
,('Urdu (Islamic Republic of Pakistan)','ur-PK')
,('Uyghur - China','ug-CN')
,('Uzbek - Uzbekistan (Cyrillic)','uz-Cyrl-UZ')
,('Uzbek - Uzbekistan (Latin)','uz-Latn-UZ')
,('Valencian - Valencia','ca-ES-valencia')
,('Vietnamese - Vietnam','vi-VN')
,('Welsh - Great Britain','cy-GB')
,('Wolof - Senegal','wo-SN')
,('Yi - China','ii-CN')


DECLARE @currencyValue decimal = 200.364
SELECT [language], FORMAT (@currencyValue, 'c', [langcode]) as FormattedDate 
FROM #temp

Conclusion

In this article, we saw different examples of functions used to change the output for different currency formats in an MS SQL database.

Note: The FORMAT function uses Common Language Runtime (CLR) and there have been noticeable performance differences between other approaches (CONVERT Function, CAST Function, etc.) showing that FORMAT is much slower.

Next Steps

For more information refer to the following links:






get scripts

next tip button



About the author
MSSQLTips author Daniel Calbimonte Daniel Calbimonte is a Microsoft SQL Server MVP, Microsoft Certified Trainer and Microsoft Certified IT Professional.

View all my tips


Article Last Updated: 2021-11-11

Comments For This Article

















get free sql tips
agree to terms