2 Ways to Format Numbers with Thousand Separators and Decimals in SQL Server

When you need to make numbers easier to read in SQL Server, adding thousand separators and controlling decimal places can make all the difference. This is especially true in reports or user-facing queries. Instead of squinting at a long string of digits, formatted output lets values like 1234567.89 appear as 1,234,567.89, making them much quicker to understand.

SQL Server offers a few ways to do this, but two stand out for their simplicity and reliability. The first is with the FORMAT() function, which gives you full control over how numbers look (and even supports different cultures). The second is the CONVERT() approach, which works well for quick, no-frills formatting when using the money data type. Both are easy to use, and I provide examples of each in this article.

Option 1. Using FORMAT()

SQL Server 2012+ introduced the FORMAT() function, which formats values based on a format string that we provide. This is probably the easiest way to format numbers in SQL Server.

The syntax goes like this:

FORMAT ( value, format_string [, culture] )

When formatting numbers, the format_string argument can be a standard numeric format string or a custom numeric format string. For our purposes, there’s a standard format string that does exactly what we want.

Here’s an example of adding the thousand separators and decimal:

DECLARE @Number DECIMAL(18, 2) = 1234567.89;

SELECT FORMAT(@Number, 'N2');

Output:

1,234,567.89

Here the 'N2'format string formats the number exactly how we want it. The N specifies that it should use standard number formatting. The (optional) precision specifier defines the number of digits to the right of the decimal point.

• The N converts the number to a string of the form “-d,ddd,ddd.ddd…”.
• The 2 precision specifier makes sure there’s only two digits to the right of the decimal point.

Example with Culture

The FORMAT() function accepts an optional culture parameter that controls the format style. This can affect the character used for the thousands separator and decimal separator. For example, some cultures use a comma (,) for the thousand separator while others use the full stop/period (.).

Here’s an example that specifies de-DE for the culture:

DECLARE @Number DECIMAL(18, 2) = 1234567.89;

SELECT FORMAT(@Number, 'N2', 'de-DE');

Output:

1.234.567,89

The de-DE culture if for Germany, which uses . for thousands and , for decimals.

Option 2. Using CONVERT() with STYLE

Another way to format numbers with thousands and decimal separators is by using the CONVERT() function with the style argument. This can only be used for money-type formatting (no full control over decimals).

Example:

DECLARE @Number MONEY = 1234567.89;

SELECT CONVERT(VARCHAR, @Number, 1);

Output:

1,234,567.89

Using a style argument of 1 adds commas and two decimal places. However, this works only if the value is of type money or smallmoney. We don’t have any way of specifying which culture to use for the formatting of thousands and decimal separators.

Quick Comparison Table

Here’s a quick table that shows the difference between the two options:

Method	Version	Culture support	Performance	Ease of use
FORMAT()	2012+	Yes	Slower	Easy
CONVERT() w/ style	All	No	Fast	Easy

Summary

• If you just want a quick, modern, and flexible way, use FORMAT(@Number, 'N2').
• If you need speed and don’t care about cultures, casting to money with CONVERT() is faster.

Formatting numbers in SQL Server is a small touch that can make a big difference in readability. FORMAT() offers flexibility and culture support, while CONVERT() provides quick simplicity for basic formatting. Keep in mind that FORMAT() is more versatile, but CONVERT() is faster and more limited. Pick the one that fits your needs, and your data will be much easier on the eyes.