Usually the number formatting will be done at the front end or the presentation layer or the application, not at the database level. However, there are several situations where you need to format a number with commas in SQL statement. Let us see a couple of ways to format the numbers in SQL statement.
1. Using the FORMAT function
The FORMAT function was introduced in SQL Server 2012. This is the most flexible and straightforward way to format numbers using patterns and culture settings.
DECLARE @Number Numeric(14,5) = 78587547.3489;
SELECT @Number,
FORMAT(@Number, 'N', 'en-US') as 'US English',
FORMAT(@Number, 'N', 'en-IN') as 'India English',
FORMAT(@Number, 'N4', 'en-US') as 'US English 4 decimals'
GO
In the above statement, FORMAT(@Number, ‘N’, ‘en-US’) formats the number to US English culture. Then the FORMAT(@Number, ‘N’, ‘en-IN’) formats the number to India English culture. By default, this will convert the number to money for the specified culture. So there will be only 2 decimal digits. To display more decimal digits, use the format pattern, with the number of decimal digits you want to display like ‘N3’ for 3 decimal digits, N4 for 4 decimal digits. The FORMAT(@Number, ‘N4’, ‘en-US’) returns the number in US English culture with 4 decimal digits.
Here is the result for the above statement:
2. Using CONVERT and CAST functions
If you are using an SQL Server version older than 2012, then you will not have the option to use FORMAT. The easiest way to format the number with commas without the FORMAT function is by using the CONVERT and CAST functions. Here is an example:
DECLARE @Number Numeric(14,5) = 78587547.3489;
SELECT @Number,
CONVERT(varchar, CAST(@Number as money), 1) as Formatted_Number
GO
The disadvantage of this method is that the decimal places are limited to two digits.
Reference
- Read more about FORMAT function at Microsoft Docs.
- Read more about CAST and CONVERT at Microsoft Docs.