Sometimes it can be useful to add thousand separators (commas) to large numbers to make them easier to view. There is no simple way to do this in SQL for an int and bigint, but it can be achieved by converting to a money type first.
The solution below gets the desired result :
SET @BigNumber =
SELECT REPLACE(CONVERT(VARCHAR,CONVERT(MONEY,@BigNumber),1), '.00','')
When run in Management Studio the result is :
This works by using the CONVERT function to convert the number from MONEY to a VARCHAR with a
style of 1, which adds the commas and also a decimal point and two decimal places. As we don't
really want the decimal places I've used the REPLACE function to strip them off.
You might prefer to encapsulate this into a User Defined Function (UDF), though if
performance is an issue a CLR should perform better.
I will finish by pointing out that formatting numbers is something that should really be
left to the presentation layer (e.g. Reporting Services or Excel) rather than SQL, which is
probably why Microsoft hasn’t included the functionality to do this. Never the less there
are times when it is convenient to present large numbers in this way within SQL Server.