Home > Uncategorized > Format prices correctly in #MSSQL using currencies in #ISO4127 format.

Format prices correctly in #MSSQL using currencies in #ISO4127 format.

If you want to specify a price including a currency, you can always write EUR 1,234 or USD 1,234, but it’s more concise to write $1,234 or €1,234, and users expect that format. You can always handle this at application layer, but here’s how to do it at Database layer.

Here, I’ve created a table of the currencies I needed, – it’s not complete, and I hope that someone can post a link to a complete table.

create table CurrencyLocales
( 
 id int identity(1,1),
 currency varchar(3),
 locale varchar(5)
)


insert into CurrencyLocales (currency,locale) values ('AUD','en-AU')
insert into CurrencyLocales (currency,locale) values ('BRL','pt-BR')
insert into CurrencyLocales (currency,locale) values ('CAD','en-CA')
insert into CurrencyLocales (currency,locale) values ('CZK','cs-CZ')
insert into CurrencyLocales (currency,locale) values ('DKK','da-DK')
insert into CurrencyLocales (currency,locale) values ('EUR','en-IE')
insert into CurrencyLocales (currency,locale) values ('GBP','en-GB')
insert into CurrencyLocales (currency,locale) values ('HUF','hu-HU')
insert into CurrencyLocales (currency,locale) values ('MXN','es-MX')
insert into CurrencyLocales (currency,locale) values ('MYR','en-MY')
insert into CurrencyLocales (currency,locale) values ('NOK','nb-NO')
insert into CurrencyLocales (currency,locale) values ('NZD','en-NZ')
insert into CurrencyLocales (currency,locale) values ('RUB','ru-RU')
insert into CurrencyLocales (currency,locale) values ('SEK','sv-SE')
insert into CurrencyLocales (currency,locale) values ('SGD','en-SG')
insert into CurrencyLocales (currency,locale) values ('USD','en-US')

create index idxCurrency on CurrencyLocales(currency)

-- example
select *, FORMAT(1234, 'C', locale) as formattedPrice  from CurrencyLocales
idCurrencyLocaleFormatted
1AUDen-AU$1,234.00
2BRLpt-BRR$1.234,00
3CADen-CA$1,234.00
4CZKcs-CZ1 234,00 Kč
5DKKda-DK1.234,00 kr.
6EURen-IE€1,234.00
7GBPen-GB£1,234.00
8HUFhu-HU1 234,00 Ft
9MXNes-MX$1,234.00
10MYRen-MYRM1,234.00
11NOKnb-NOkr 1 234,00
12NZDen-NZ$1,234.00
13RUBru-RU1 234,00 ₽
14SEKsv-SE1.234,00 kr
15SGDen-SG$1,234.00
16USDen-US$1,234.00
Advertisement
Categories: Uncategorized
  1. No comments yet.
  1. No trackbacks yet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

%d bloggers like this: