Posted By: Anonymous
I’m running some administrative queries and compiling results from
sp_spaceused in SQL Server 2008 to look at data/index space ratios of some tables in my database. Of course I am getting all sorts of large numbers in the results and my eyes are starting to gloss over. It would be really convenient if I could format all those numbers with commas (987654321 becomes 987,654,321). Funny that in all the many years I’ve used SQL Server, this issue has never come up since most of the time I would be doing formatting at the presentation layer, but in this case the T-SQL result in SSMS is the presentation.
I’ve considered just creating a simple CLR UDF to solve this, but it seems like this should be do-able in just plain old T-SQL. So, I’ll pose the question here – how do you do numeric formatting in vanilla T-SQL?
In SQL Server 2012 and higher, this will format a number with commas:
select format([Number], 'N0')
You can also change
0 to the number of decimal places you want.