Format Number with Commas in SQL Server

by

In this SQL Server tutorial, you will learn how to format number with commas in SQL Server. Where you will understand how to use FORMAT() function to format the number.

Also, you will use the different format specifier with the FORMAT() function to format the value with commas.

Format Number with Commas in SQL Server

When you format the number with a comma, it makes it more readable. Consider you have a number like this 1000000, seeing a number like that is not very meaningful. If you format the same number such as 1,000,000, look how it appears now.

So, you format the number for better readability. Formatting the number with commas not only changes its appearance but also helps the user to understand it in a better way.

For formatting the number with commas, you will use the format function, and the syntax is given below.

FORMAT(value_or_column, 'N0')

Where,

  • FORMAT(): It is the function that formats the number based on the ‘N0’ format specifier.
  • value_or_column: The value or the column containing the value that you want to format with commas.
  • ‘N0’: It is the format specifier or pattern that formats with no decimals places.

Let’s take a simple example of how to use the FORMAT() function to format the given number.

Open the query editor of SQL Server, you can use SQLCMD or SSMS. Run the below query.

SELECT FORMAT(1000000, 'N0');
Format Number with Commas in SQL Server Using Query

Look at the above picture that contains the output of the query where the number ‘1000000’ is formatted with commas as 1,000,000.

Now let’s move to see how to use the FORMAT() function with table column to format numbers with commas.

Format Number with Commas in SQL Server: Two Decimal Places

You can use the FORMAT() function to format the given number with commas and two decimal places. For that, the ‘N2’ format specifier is used with FORMAT() functions.

For example, suppose you work with the financial databases of a company in the USA. The financial database stores the transactions, balances, and other monetary values.

You have been assigned a task to generate the financial reports for stakeholders, in the reports, make sure that the large numbers should be formatted with commas for better readability.

The financial database has a FinancialAccounts table with columns AccountID, AccountName, and Balance. The table is shown below.

Format Number with Commas in SQL Server Two Decimal Places FinancialAccounts Table

Now let’s see how to format the number with commas and two decimals places using the below query.

SELECT AccountName, FORMAT(Balance, 'N2') as FormatteBalance
FROM FinancialAccounts;
Format Number with Commas in SQL Server Two Decimal Places

Look at the above output, how the number within the Balance column is formatted with commas and two decimal places. For example, the Checking Account balance is formatted as 2,500.75, and the Investment Portfolio is 50,000,000.00.

In the same way, all other AccountName balances are formatted with commas as you can see in the above output FormatteBalance column.

In the query part FORMAT(Balance, ‘N2’), N means to format the given value as a number, and 2 means putting two decimal places in the number. If you want three decimals, then use N3, and for more decimal places N4, N5, etc.

Format Number with Commas in SQL Server Based on Locale

If you want to format the number with commas based on your locale or language, you can specify the locale with the FORMAT() function.

For that, you need to set the locale or language using the below syntax.

FORMAT(value_or_column, 'N0', 'localeName')

Where,

  • FORMAT(): It is the function that formats the number based on the ‘N0’ format specifier.
  • value_or_column: The value or the column containing the value that you want to format with commas.
  • ‘N0’: It is the format specifier or pattern that formats with no decimals places.
  • localeName: Name of the locale or language according to a specific region.

Now suppose you work in an e-commerce platform company and you have access to the e-commerce database and that database contains a Sales table that stores sales figures for various products.

There is a need to make decisions on the sales data by analyzing it, so here you have to format the total sales value in a more readable way. The table is shown below.

Now run the below query to format the value within the TotalSales column with commas based on the ‘en-US’ locale.

SELECT SaleID, Format(TotalSales,'N0','en-US') as LocaleFormatted 
FROM Sales;
Format Number with Commas in SQL Server Based on Locale

Look in the above output, all the values within the TotalSales column are formatted with commas based on the locale ‘en-US’ which stands for US English. For example, look at the LocaleFormatted column in the above output of the query.

This is how you can format number with commas in SQL Server based on your locale.

Format Number with Commas in SQL Server Using Custom Format Specifier

You can also specify your own format with FORMAT() function to format number with commas in SQL Server.

For example, let’s take the same example that you have used in the above sub-section, Where you need to format the number within the TotalSales column of the Sales table.

For that, use the below query.

SELECT SaleID, Format(TotalSales,'##,###') as CustomFormatting 
FROM Sales;
Format Number with Commas in SQL Server Using Custom Format Specifier

Now look at the custom format pattern, which is ‘##,###’ where each # character represents the digit placeholder and the comma (,) which represents the group separator.

Here you can also include the decimal point (.) such as ‘###,###.##’ for the larger numbers.

When you execute the above query, it formats the number with commas as specified in the pattern. For example, compare the digit and commas position in the number and pattern (12,5000 and ‘##,###’).

This is how to format number with commas in SQL Server using the custom format pattern.

Conclusion

In this SQL Server tutorial, you learned how to format the number with commas in sql server. Where you learned about FORMAT() function with different format patterns such as N2, locale, and custom format (###,###).

You may also like: