How do I format a number with commas in T-SQL?

Question

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?

Answer 1

here is another t-sql UDF

CREATE FUNCTION dbo.Format(@num int)
returns varChar(30)
As
Begin
Declare @out varChar(30) = ''

  while @num > 0 Begin
      Set @out = str(@num % 1000, 3, 0) + Coalesce(','+@out, '')
      Set @num = @num / 1000
  End
  Return @out
End

Answer 2

This belongs in a comment to Phil Hunt's answer but alas I don't have the rep.

To strip the ".00" off the end of your number string, parsename is super-handy. It tokenizes period-delimited strings and returns the specified element, starting with the rightmost token as element 1.

SELECT PARSENAME(CONVERT(varchar, CAST(987654321 AS money), 1), 2)

Yields "987,654,321"