There is no simple way to format numbers adding thousand separators (commas) to large numbers to make them easier to view in SQL for an int, floats or any other numeric data type.
- Use CONVERT function to convert the number to MONEY type
- Use CONVERT function to convert MONEY to a VARCHAR with a style of 1, which adds the commas, decimal point and two decimal places
- Use REPLACE to strip decimal points and numbers off
DECLARE @MyNumber BIGINT SET @MyNumber = 111222333444 SELECT REPLACE(CONVERT(VARCHAR,CONVERT(MONEY,@BigNumber),1), '.00','')
The result is :
However we still have a problem when 1 (one) decimal number after the decimal point is required. That is where I found a script to create a function to format any number to the custom format. The function takes two parameters,
- Number to be formatted, ex : (123456789.99999)
- Required Format, ex : (99,99,99,999.99)
It returns formatted number as output ex : (12,34,56,789.99)
My conversion now looks like this:
Ideally all numbers formatting should be done in the presentation layer like Reporting Services or Excel rather than in SQL. My requirement was different as I had a script which dynamically generated HTML page, hence I formatted numbers in SQL script.