Format 'int' datatype column with commas

[matt23lucier]

Does anyone know how I can format a column (with datatype 'int') to have commas after every 3 numbers. For example 2500. I would like to format this to 2,500 when it is returned from a select statement.
thanks
Matt

 

[gmmastros]

faq183-6157

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[matt23lucier]

gmmastros,
Thanks for the link but the link does not explain how to format a column of datatype 'int' to have commas every three numbers.

 

[gmmastros]

You are absolutely right!

It does show how to convert a decimal to a value that has commas every 3 space though. Directly from the link...

[COLOR=blue]DECLARE[/color] @v2 [COLOR=blue]DECIMAL[/color] (36,10)
[COLOR=blue]SELECT[/color] @v2 = 13243543.56565656

[COLOR=blue]SELECT[/color] [COLOR=#FF00FF]CONVERT[/color]([COLOR=blue]VARCHAR[/color],[COLOR=#FF00FF]CONVERT[/color]([COLOR=blue]MONEY[/color],@v2),1) [COLOR=green]--13,243,543.57 [/color]

Converted to work with integers....

[COLOR=blue]DECLARE[/color] @v2 [COLOR=blue]Int[/color]
[COLOR=blue]SELECT[/color] @v2 = 13243543

[COLOR=blue]SELECT[/color] [COLOR=#FF00FF]CONVERT[/color]([COLOR=blue]VARCHAR[/color],[COLOR=#FF00FF]CONVERT[/color]([COLOR=blue]MONEY[/color],@v2),1) [COLOR=green]--13,243,543.00 [/color]

Notice that the only thing I changed was the test variable. The point you need to realize is that integers CANNOT have commas. in fact, only varchars can. So, to do this, you need to convert to the money data type first, and then convert to varchar (with the extra style parameter).

Does this help?

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[SQLDenis]

I mean come on you have to do some work yourself!!
You can not have everything presented to you on a silver platter
Have you tried using int for example like I will show you below? Or are you just a Copy and Paste kind of guy/gal?

DECLARE @v2 int
SELECT @v2 = 13243543

SELECT CONVERT(VARCHAR,CONVERT(MONEY,@v2),1) --13,243,543.00

Denis The SQL Menace
--------------------
SQL Server Code,Tips and Tricks, Performance Tuning
Google Interview Questions

 

[ptheriault]

This is what I came up with. George led you to water but you have to do some work for yourself too.

declare @i int

set @i = 1555000

select substring(convert(varchar(15),convert(money,@i),1),1,CHARINDEX('.',convert(money,@i))+1)

- Paul

- If at first you don't succeed, find out if the loser gets anything.

 

[gmmastros]

And... to remove the decimal point and the zero's....

[COLOR=blue]DECLARE[/color] @v2 [COLOR=blue]Int[/color]
[COLOR=blue]SELECT[/color] @v2 = 13243543

[COLOR=blue]SELECT[/color] ParseName([COLOR=#FF00FF]CONVERT[/color]([COLOR=blue]VARCHAR[/color],[COLOR=#FF00FF]CONVERT[/color]([COLOR=blue]MONEY[/color],@v2),1), 2) [COLOR=green]--13,243,543.57 [/color]

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[matt23lucier]

I think I may have finally gotten it. Thank you everyone for your help. It's not like I wanted everything handed to me, I just could not get it to work. After you (gmmastros) sent me the first link I tried to do other things but could not get the formatting correct. Anyways I appreciate your help.
Matt