How to Format Number with Commas and Decimal in SQL Server?

In this SQL Server tutorial, I will show you how to format number with commas and decimal in SQL Server.

In particular, you will learn about the SQL Server FORMAT() function that helps in formatting the given numbers based on the provided format pattern.

You can format the number with different format patterns, but here, you will learn how to format numbers with commas and decimals only.

Table of Contents

Format Number with Commas and Decimal in SQL Server

Numbers are used everywhere in your daily life, and by using the number, you can give precise meaning to anything. Suppose you walk 3km daily which means you walk exactly 3km every day, not more and less.

When you buy something, that amount is represented using a number like an onion is 2 dollars per kg, etc. In the case of a database, you often store information related to quantitative data such as measurements, financial value, etc.

So if you present the data or number in its raw form, it will not make any sense. First, you need to format the number or data so that it makes sense. So here, making sense means, if you say you walk 3, here is what 3 means: do you walk 3km or 3 miles?

In the same way, numbers or data exist in raw form in your database, so when you need to generate reports and present data, you need to change it into a meaningful format so that it should convey some meaningful information.

So, in this section, you will learn to format number with commas and decimal in SQL Server.

Format Number with Commas and Decimal in SQL Server using Format() Function

In SQL Server, the FORMAT() function allows you to format the given number in the specified format.

The syntax is given below.

FORMAT(num_value, format)

Where,

num_value: It is the numeric value or expression that you want to format in a specific pattern.
format: The format pattern which decides how the provided numeric values should be formatted or displayed.

You can use the above syntax of the FORMAT() function to format any number. Now, suppose you have a Product_Sales table with columns ProductName and SalesPrice, which is shown below.

Format Number with Commas and Decimal in SQL Server using Format() Function

As you can see in the SalePrice column, the price for each product is not in a meaningful way. So here, you have to format each number within the SalePrice column with commas (thousand separators) and three decimal places.

So, for that, use the below query.

SELECT ProductName, FORMAT(SalePrice,'N3') as FormattedNumber 
FROM Product_Sales;

format number with commas and decimal in SQL Server

After the execution of the above query, it puts the commas, which act as thousand separators, and after the decimal, it shows three digits, for example, 999,999.990 for iPhone 13 Pro.

In the query part FORMAT(SalePrice,’ N3′), the N3 is the format specified or pattern that is applied to the SalePrice column,. N3 means add the thousand separators (commas) and displaying three decimal places for the number within the SalePrice column.
If you use the format pattern as N2 in the FORMAT() function, then it displays the number value with two decimal places with a thousand separators. So here, the number (2, 3, etc.) after the N represents the number of digits you want to put after the decimal in any numeric value.

Format Number with Commas and Decimal in SQL Server using Format() Function with Custom Format

You can also provide a custom format pattern to the FORMAT() function in SQL Server. Now, again, you want to format each number within the SalePrice column with commas (thousand separators) and four decimal places. For that, use the below query.

SELECT ProductName, FORMAT(SalePrice,'###,###.0000') as FormattedNumber 
FROM Product_Sales;

Format Number with Commas and Decimal in SQL Server using Format() Function With Custom Pattern

As you can see in the above output, each number within the SalePrice column contains a comma (which is a thousand separators in this case) and a 4-digit number after the decimal point.

Let’s understand the custom format ‘###,###.0000’ in the FORMAT() function.

Here, the ‘###,###’ means dividing the numeric value into groups of three digits and separating them using (comma).
Then (.0000) means display four decimal places or display only the four digits after the decimal point in a numeric value.
For example, before formatting, the sale price of the Samsung Galaxy S22 was 899999.99, and after formatting, it became 899,999.9900, as you can see in the above output.

In the original sale price, there were only two digits (.99) after the decimal point, but after formatting, extra zeros were added to represent the four decimal places (.9900).

Format Number with Commas and Decimal in SQL Server using Format() Function with Locale

Locale means a particular region, and based on the regional area, you can format the number. The FORMAT() function accepts the third parameter, which is the locale or language code.

Suppose you live in the USA and want to format the numbers with commas and decimal places based on your current location. For that, use the below query.

SELECT ProductName, FORMAT(SalePrice,'N','en-US') as FormattedNumber 
FROM Product_Sales;

Format Number with Commas and Decimal in SQL Server using Format() Function with Locale

The numeric value of the SalePrice column is formatted with commas and two decimal places based on the English (United States) locale. Here the third parameter, ‘en-US’ represents the English (United States) locale.

If you provide the locale as ‘fr-FR’ to the FORMAT() function, then it will format the numeric value with commas and decimal places based on the French(France) locale.

So you can format the number based on your current locale. I hope that you have understood how to format the numbers with commas and decimals in SQL Server.

Conclusion

In this SQL Server tutorial, you learned how to format a number with commas (thousand separators) and decimal places in sql server. Where you learned different patterns to format the number, such as predefined (N, N2, and N3), custom format pattern, and locale.