Format Number with Thousands Separator in SQL Server

by

In this SQL Server tutorial, I will explain to you how to format number with thousand separator in SQL Server.

You will understand use of the functions such as FORMAT(), CONVERT(), and CAST() to format numbers with thousand separators.

Format Number with Thousands Separator in SQL Server

While querying the information from the database, how we represent the data always matters. When dealing with numeric information such as financial reports, ensuring the readability of these numeric values for better understanding or communication is very important.

So if you are dealing with large numbers without the thousand separators, knowing the magnitude of the values can be tough.

For example, look at these two numbers 10000 and 100000. If you write these numbers as 10,000 and 100,000, look how the more meaningful these numbers appear.

So including the thousand separators to a number, increases the readability and visual attraction. Let’s see how to format number with thousands separator in SQL Server

Format Number with Thousands Separator in SQL Server: using Format()

The FORMAT() function formats the given value on the basis of the format strings. So you can use this function to format the number with a thousand separator, and this thousand separator is represented as a comma (,).

The format string or specifier that you are going to use is the ‘N’ which means format the given value as a number and it also includes the thousand separator to that number.

Suppose you are an employee of the bank in the United States, and have access to the FinancialAccounts table which is shown below.

Format Number with Thousands Separator in SQL Server using Format() Function FinanceAccounts Table

Here you have to use the FORMAT() function to format or present the balance with a thousand separators for better readability. For that use the below query.

Select AccountID, AccountName, FORMAT(Balance,'N') 
FROM FinancialAccounts;
Format Number with Thousands Separator in SQL Server using Format() Function

As from the above output of the query, each balance is formatted with a thousand separator (comma). For example, the Mortgage is -300,000.00.

So here you need to understand the format string or specifier which is ‘N’ that instructs SQL Server to format the given number with a thousand separator.

Whenever you are going to format the for thousand separators, use the ‘N’ as the format specifier within the FORMAT() function.

But using the FORMAT() function, you can also format the number with a thousand separator using the custom format string. For example, use the below query.

SELECT AccountID, AccountName, FORMAT(Balance,'###,###,###.##') 
FROM FinancialAccounts;
Format Number with Thousands Separator in SQL Server using Format() Custom Format String

You again got the same output when used the ‘N’ as the format specifier within the FORMAT() function. But here you have used a different format string which is ‘###,###,###.##’.

Let’s decode this format strings,

  • The # character or symbol represents the placeholder for the digit and it can be any digit from 0 to 9.
  • The ‘,’ is the thousand separator (comma) and it separates the group of digits in large numbers, in this case, separating the group of the three digits based on the custom format.

So when you use the format string like this ‘###,###,###.##’ that means SQL Server is going to format the number with a comma (thousand separators).

This is how to format the numbers with a thousand separator using the FORMAT() function the ‘N’ and custom format strings.

Format Number with Thousands Separator in SQL Server: using Convert()

CONVERT() function converts the datatype of any value to another datatype. So here first, you will convert the datatype of the number to monetary datatype and format that number with a thousand separator.

Let’s understand through an example, suppose you have access to the Product_Sales table which is shown below.

Format Number with Thousands Separator in SQL Server using Convert() Table Product Sales

Now you want to format the SalePrice column number with a thousand separators. For that use the below query.

SELECT SaleID, ProductName, 
CONVERT(VARCHAR, CONVERT(MONEY, SalePrice), 1) As FormattedWithThousandSep
FROM Product_Sales;
Format Number with Thousands Separator in SQL Server using Convert()

Look at the above output, the numbers of the SalePrice are formatted with a thousand separator. For example, the Dell XPS 13 Laptop price is 119,999.99. If you compare this price value to the above table price, after formatting the number it contains the thousand separator.

Let’s understand the query part CONVERT(VARCHAR, CONVERT(MONEY, SalePrice), 1):

First, convert the column datatype to the money datatype using CONVERT(MONEY, SalePrice). The purpose of converting the column value to money datatype is because it allows you to format the numeric values in a specific way

Again converting the money datatype value with style equals 1 to the VARCHAR datatype using CONVERT(VARCHAR,…,1) When the money datatype is converted with style 1, it automatically adds the thousand separators to that number.

So that you get the numeric value with a thousand separator in the above picture. This is how you can use the CONVERT() function to format the number with a thousand separators.

Format Number with Thousands Separator in SQL Server: using Cast()

The CAST() function also changes the datatype of the value to another datatype. Now, take the same example as shown in the above section. So execute the below query.

SELECT SaleID, ProductName, 
CONVERT(VARCHAR, CAST(SalePrice AS MONEY), 1) As FormattedWithThousandSep
FROM Product_Sales;
Format Number with Thousands Separator in SQL Server using Cast()

Here only difference is the use of the CAST() function to convert the datatype of the SalePrice column to the MONEY datatype. After execution of the above query, it also formats the number with a thousand separator.

This is how to use the CAST() function with the CONVERT() function to format the number with a thousand separator.

These are the ways that you can use to format number with thousands separator in SQL Server

Conclusion

In this SQL Server tutorial, you covered how to use the FORMAT() and CONVERT() functions to format the column numeric values with a thousand separator. Also used the CAST() function while formatting the number with a thousand separator.

You may also like to read: