When working with financial data in SQL Server, you may occasionally need to present numbers as formatted currency values. Storing currency amounts as DECIMAL or MONEY types is common, but these result in raw numbers like 1234.5, which don’t tell users which currency it’s in. With a bit of formatting logic, you can make query results easier to read and more meaningful.
Example
Let’s start with a small dataset so we can run a quick example to demonstrate.
Imagine you’re tracking subscription revenues for a SaaS product in different markets. We’ll insert data with three currencies: USD, EUR, and AUD.
CREATE TABLE Revenues (
Id INT IDENTITY PRIMARY KEY,
CustomerName NVARCHAR(100),
Amount DECIMAL(12,2),
CurrencyCode CHAR(3)
);
INSERT INTO Revenues (CustomerName, Amount, CurrencyCode)
VALUES
('TechNova Inc.', 1250.75, 'USD'),
('Müller GmbH', 890.40, 'EUR'),
('KoalaSoft Pty Ltd', 1575.90, 'AUD'),
('BrightPath LLC', 312.00, 'USD'),
('EuroVision SA', 4599.99, 'EUR'),
('Harbor IT', 745.20, 'AUD');So we now have a mix of customers with revenue amounts tied to different currencies.
SQL Server doesn’t automatically know how you want to display a currency code or symbol. But you can use a simple CASE expression along with the FORMAT() function to produce clean results:
SELECT
CustomerName,
CASE CurrencyCode
WHEN 'USD' THEN FORMAT(Amount, 'C', 'en-US')
WHEN 'EUR' THEN FORMAT(Amount, 'C', 'de-DE')
WHEN 'AUD' THEN FORMAT(Amount, 'C', 'en-AU')
ELSE CAST(Amount AS NVARCHAR(50))
END AS FormattedAmount,
CurrencyCode
FROM Revenues;Result:
CustomerName FormattedAmount CurrencyCode
-------------------- ---------------- ------------
TechNova Inc. $1,250.75 USD
Müller GmbH 890,40 € EUR
KoalaSoft Pty Ltd $1,575.90 AUD
BrightPath LLC $312.00 USD
EuroVision SA 4.599,99 € EUR
Harbor IT $745.20 AUD
Not only do we have a separate column for the currency, but we also use the values in that column to determine how to output the formatted amount.
Here’s how it works:
FORMAT(Amount, 'C', 'en-US'): The'C'format specifier tells SQL Server to use currency formatting. The third parameter sets the culture, which controls which symbol and number style is used.'en-US'results in$1,250.75'de-DE'results in890,40 €'en-AU'results in$1,575.90(with Australian conventions)
- The
CASEexpression lets you handle different currencies explicitly. This is more reliable than assuming one locale for all rows.
Adding More Currencies
The FORMAT() function can handle many cultures, not just USD, EUR, or AUD. Let’s add a few more currencies into the mix: GBP (British Pound), JPY (Japanese Yen), CAD (Canadian Dollar), INR (Indian Rupee), and ZAR (South African Rand):
INSERT INTO Revenues (CustomerName, Amount, CurrencyCode)
VALUES
('Finch Analytics Ltd', 2200.10, 'GBP'),
('Sakura Systems', 150000, 'JPY'),
('MapleTech Corp.', 3050.75, 'CAD'),
('NextGen Solutions', 98500.25, 'INR'),
('UbuntuSoft', 13499.99, 'ZAR');Now let’s extend the CASE expression to include them:
SELECT
CustomerName,
CASE CurrencyCode
WHEN 'USD' THEN FORMAT(Amount, 'C', 'en-US') -- US Dollar
WHEN 'EUR' THEN FORMAT(Amount, 'C', 'de-DE') -- Euro
WHEN 'AUD' THEN FORMAT(Amount, 'C', 'en-AU') -- Australian Dollar
WHEN 'GBP' THEN FORMAT(Amount, 'C', 'en-GB') -- British Pound
WHEN 'JPY' THEN FORMAT(Amount, 'C', 'ja-JP') -- Japanese Yen
WHEN 'CAD' THEN FORMAT(Amount, 'C', 'en-CA') -- Canadian Dollar
WHEN 'INR' THEN FORMAT(Amount, 'C', 'en-IN') -- Indian Rupee
WHEN 'ZAR' THEN FORMAT(Amount, 'C', 'en-ZA') -- South African Rand
ELSE CAST(Amount AS NVARCHAR(50))
END AS FormattedAmount,
CurrencyCode
FROM Revenues;Running this query will yield results like:
CustomerName FormattedAmount CurrencyCode
-------------------- --------------- ------------
TechNova Inc. $1,250.75 USD
Müller GmbH 890,40 € EUR
KoalaSoft Pty Ltd $1,575.90 AUD
BrightPath LLC $312.00 USD
EuroVision SA 4.599,99 € EUR
Harbor IT $745.20 AUD
Finch Analytics Ltd £2,200.10 GBP
Sakura Systems ¥150,000 JPY
MapleTech Corp. $3,050.75 CAD
NextGen Solutions ₹ 98,500.25 INR
UbuntuSoft R13 499,99 ZAR
By mapping each CurrencyCode to the correct culture code, you make sure amounts appear natural to users in different regions. If you start dealing with even more markets, you can simply extend the CASE block with additional currencies. Either that, or you could replace that long CASE with a lookup table of currency codes and cultures, so you can maintain it more easily instead of editing queries.
Using a Lookup Table for Currency Formatting in SQL Server
Instead of hardcoding culture codes into your query, you can store them in a reference table. That way, adding new currencies is just a matter of inserting a row instead of editing SQL logic:
CREATE TABLE CurrencyFormats (
CurrencyCode CHAR(3) PRIMARY KEY,
CultureCode NVARCHAR(10)
);
INSERT INTO CurrencyFormats (CurrencyCode, CultureCode)
VALUES
('USD', 'en-US'), -- US Dollar
('EUR', 'de-DE'), -- Euro
('AUD', 'en-AU'), -- Australian Dollar
('GBP', 'en-GB'), -- British Pound
('JPY', 'ja-JP'), -- Japanese Yen
('CAD', 'en-CA'), -- Canadian Dollar
('INR', 'en-IN'), -- Indian Rupee
('ZAR', 'en-ZA'); -- South African RandThis table defines which .NET culture code to use for each currency.
Now you can join Revenues with CurrencyFormats and use the CultureCode dynamically:
SELECT
r.CustomerName,
FORMAT(r.Amount, 'C', cf.CultureCode) AS FormattedAmount,
r.CurrencyCode
FROM Revenues r
JOIN CurrencyFormats cf
ON r.CurrencyCode = cf.CurrencyCode;Result:
CustomerName FormattedAmount CurrencyCode
-------------------- --------------- ------------
TechNova Inc. $1,250.75 USD
Müller GmbH 890,40 € EUR
KoalaSoft Pty Ltd $1,575.90 AUD
BrightPath LLC $312.00 USD
EuroVision SA 4.599,99 € EUR
Harbor IT $745.20 AUD
Finch Analytics Ltd £2,200.10 GBP
Sakura Systems ¥150,000 JPY
MapleTech Corp. $3,050.75 CAD
NextGen Solutions ₹ 98,500.25 INR
UbuntuSoft R13 499,99 ZAR
Same result as before, but with a more concise query.
This has some pretty decent advantages over the CASE method:
- Scalable: Adding new currencies is just an
INSERTintoCurrencyFormats. - Cleaner Queries: No long
CASEblock to maintain. - Separation of Concerns: Business rules (which culture to use) are in one place, not scattered across queries.
Either way, both approaches allow you to get the same result.