Formatting Numbers for International Users in SQL Server (Locale-Aware)

by

When working with applications that serve people across different countries, you quickly realize that numbers aren’t always written the same way. A salary of 55,000.75 in the U.S. might be displayed as 55.000,75 in Germany or 55 000,75 in France. The decimal and thousands separators change depending on a user’s locale.

If you’re storing numbers in SQL Server but want to display them in a format that makes sense internationally, you’ll want to tap into SQL Server’s locale-aware formatting.

Why Locale Matters

Imagine pulling together reports on sales revenue. If you ship globally, you might send a report to your French office showing 1,200,000.50 in revenue. To them, that looks odd, because commas are for decimals in French formatting. Misreading numbers can cause serious confusion. SQL Server includes functions that let you format numbers according to different cultures (locales), so you can avoid misunderstandings.

The FORMAT() Function

Fortunately, SQL Server provides us with the FORMAT() function, which allows us to take a numeric or date value and apply .NET-style formatting. We can pass in a culture string like en-US, fr-FR, or de-DE to get the right number representation.

Example

Below is an example that uses the FORMAT() function to demonstrate how we can display the same number using different locales.

Example Dataset

Let’s set up a small table of project budgets. We’ll store plain numeric values, then query them in multiple formats:

CREATE TABLE ProjectBudgets
(
    ProjectID INT PRIMARY KEY,
    ProjectName NVARCHAR(100),
    BudgetAmount DECIMAL(18,2)
);

INSERT INTO ProjectBudgets (ProjectID, ProjectName, BudgetAmount)
VALUES
(1, 'Renewable Energy Study', 1250000.75),
(2, 'Urban Transit Expansion', 98765432.10),
(3, 'AI Research Program', 55200.5),
(4, 'Cultural Heritage Archive', 785000.99);

SELECT * FROM ProjectBudgets;

Output:

ProjectID  ProjectName                BudgetAmount
---------  -------------------------  ------------
1          Renewable Energy Study     1250000.75  
2          Urban Transit Expansion    98765432.1  
3          AI Research Program        55200.5     
4          Cultural Heritage Archive  785000.99

We can see that the data is stored without any locale formatting. In particular, the numbers are stored without a thousands separator. However, they do have a decimal separator, due to the column being defined as DECIMAL(18,2).

Anyway, now that we have some data, we can see how formatting changes with different locales.

Locale-Aware Queries

Here’s a query that shows the same number in U.S., German, and French formatting:

SELECT
    ProjectName,
    BudgetAmount,
    FORMAT(BudgetAmount, 'N', 'en-US') AS Budget_US,
    FORMAT(BudgetAmount, 'N', 'de-DE') AS Budget_DE,
    FORMAT(BudgetAmount, 'N', 'fr-FR') AS Budget_FR
FROM ProjectBudgets;

Result:

ProjectName               BudgetAmount  Budget_US      Budget_DE      Budget_FR
----------------------    ------------  -------------  -------------  -------------
Renewable Energy Study    1250000.75    1,250,000.75   1.250.000,75   1 250 000,75
Urban Transit Expansion   98765432.10   98,765,432.10  98.765.432,10  98 765 432,10
AI Research Program       55200.50      55,200.50      55.200,50      55 200,50
Cultural Heritage Archive 785000.99     785,000.99     785.000,99     785 000,99

We can see that each locale has its own convention for displaying the thousands and decimal separators.

Things to Keep in Mind

  • Performance: While FORMAT() can be very handy for situations like this, it’s not exactly the fastest function. For very large queries or high-throughput systems, you may want to format at the application layer instead of in SQL.
  • Data vs. Presentation: Store numbers in a standard numeric format. Formatting should be applied only when you need to display data to users.
  • Culture Strings: The culture codes are standard .NET culture identifiers. These are different to the ones returned by the sys.syslanguages view. See Microsoft’s documentation for the .NET CultureInfo class for more information.

Wrapping Up

International users expect numbers in formats they understand. SQL Server’s FORMAT() function makes it easy to display locale-aware values without manually swapping commas and periods. By keeping the raw data consistent and formatting only when necessary, you make your database more user-friendly across borders.