CODEWITHSUNDEEP
sqlsql-server-2008
roxrook
roxrook

Reputation: 13853

How to add commas every 3 digits character for string in Microsoft Server SQL?

Is there a built-in function for this task? If not, how can it be done? It is varchar(20), and it only contains integers. For example, 309000 -> 309,000.

Any idea?

Thanks,

Upvotes: 0

Views: 3635

Answers (2)

Sarah Allen
Sarah Allen

Reputation: 61

You may use

SELECT FORMAT(2123456789, '#,#')

Upvotes: 0

RichardTheKiwi
RichardTheKiwi

Reputation: 107736

You can use cascading CASE statements, which can be put into a function

case
when i > 999999999 then STUFF(STUFF(STUFF(i,2,0,','),6,0,','),10,0,',')
when i > 999999 then STUFF(STUFF(i,LEN(i)-5,0,','),len(i)-1,0,',')
when i > 999 then STUFF(i,LEN(i)-2,0,',')
else CONVERT(varchar(10),i)
end

Note Bear in mind the data returned is no longer a number, so your front-end code won't be able to use it as a number. I would always advise for the formatting to be done via the front-end code.

e.g.

select col1, col2, col3,
    case
    when i > 999999999 then STUFF(STUFF(STUFF(i,2,0,','),6,0,','),10,0,',')
    when i > 999999 then STUFF(STUFF(i,LEN(i)-5,0,','),len(i)-1,0,',')
    when i > 999 then STUFF(i,LEN(i)-2,0,',')
    else CONVERT(varchar(10),i)
    end int_with_commas_as_varchar
from tbl

or create a function

create function dbo.formatThousands(@i int)
returns varchar(20) as
begin
return
    case
    when @i > 999999999 then STUFF(STUFF(STUFF(@i,2,0,','),6,0,','),10,0,',')
    when @i > 999999 then STUFF(STUFF(@i,LEN(@i)-5,0,','),len(@i)-1,0,',')
    when @i > 999 then STUFF(@i,LEN(@i)-2,0,',')
    else CONVERT(varchar(10),@i)
    end
end

Upvotes: 1

Related Questions