SQL Server Integer Formatting

Question

I have a stored procedure that queries some data and emails it, I'd like to change the procedure so that any numbers in the thousands get displayed with commas like 1,000. I guess the best way of doing this in SQL is to convert it to a string and add the comma?

Thanks

Answer 1

Correct. An integer has no formatting by definition of the data type.

So you must either convert the value to a string(varchar for example) or as is the more common practice, have the presentation tier handle the formatting of the value.

Answer 2

If you're stuck using SQL Server as the presentation of the email (no other report application),

SELECT CONVERT(varchar(24),CONVERT(money,CAST( 12345678 AS VarChar(24))),1)

Answer 3

Here's a relatively clean answer. Convert to money (no data type loss). Then convert to varchar with style 1 (it does the commas for you) and then remove the .00 from the end, since it comes with the money data type.

select REPLACE(CONVERT(VARCHAR, CONVERT(MONEY, 1234), 1), '.00', '')

It's a fine line to figure out how many string manipulations before doing an inline function, but this should be a pretty quick conversion.

Thanks, Eric