How do I convert a number to a numeric, comma-separated formatted string?

Question

Is there an easy way to convert a number (in my case an integer) to a comma separated nvarchar string?

For instance, if I had an int value of 1000000 stored in a field, how can I convert it to an nvarchar string with the outputted result of "1,000,000"?

I could easily write a function to do this but I wanted to be sure there wasn't an easier way involving a call to either CAST or CONVERT.

Answer 1

The reason you aren't finding easy examples for how to do this in T-SQL is that it is generally considered bad practice to implement formatting logic in SQL code. RDBMS's simply are not designed for presentation. While it is possible to do some limited formatting, it is almost always better to let the application or user interface handle formatting of this type.

But if you must (and sometimes we must!) use T-SQL, cast your int to money and convert it to varchar, like this:

select convert(varchar,cast(1234567 as money),1) 

If you don't want the trailing decimals, do this:

select replace(convert(varchar,cast(1234567 as money),1), '.00','') 

Good luck!

Answer 2

For SQL Server 2012, or later, an easier solution is to use FORMAT ()^{Documentation}.
EG:

SELECT Format(1234567.8, '##,##0')  

Results in: 1,234,568