CODEWITHSUNDEEP
sql-serversql-server-2005
Gavin
Gavin

Reputation: 17382

SQL Server Integer Formatting

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

Upvotes: 0

Views: 688

Answers (3)

Anon246
Anon246

Reputation: 1841

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

Upvotes: 2

JeffO
JeffO

Reputation: 8043

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)

Upvotes: 0

John Sansom
John Sansom

Reputation: 41819

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.

Upvotes: 1

Related Questions