How to format numbers (int) to separate hundreds, thousands etc with c

[sivi]

Could someone help me with the following:

I need to format numeric values with commas to separate hundreds, thousands, millions etc.

Could someone let me know how to do this please.

Many Thanks
Sivi

 

[MaffewW]

this should work I think up to 1000000000


SELECT yourfield,len(yourfield),
(CASE
when len(yourfield) = 5 then left(yourfield,2) + ',' + right(yourfield,3)
when len(yourfield) = 6 then left(yourfield,3) + ',' + right(yourfield,3)
when len(yourfield) = 7 then left(yourfield,1) + ',' + substring(convert(varchar,yourfield),2,3) + ',' + right(yourfield,3)
when len(yourfield) = 8 then left(yourfield,2) + ',' + substring(convert(varchar,yourfield),3,3) + ',' + right(yourfield,3)
when len(yourfield) = 9 then left(yourfield,3) + ',' + substring(convert(varchar,yourfield),4,3) + ',' + right(yourfield,3)
when len(yourfield) = 10 then left(yourfield,1) + ',' + substring(convert(varchar,yourfield),2,3) + ',' + substring(convert(varchar,yourfield),5,3) + ',' + right(yourfield,3)
else convert(varchar,yourfield)
end)

FROM yourtable

Matt

Brighton, UK

 

[MaffewW]

SELECT
(CASE
when len(yourfield) = 5 then left(yourfield,2) + ',' + right(yourfield,3)
when len(yourfield) = 6 then left(yourfield,3) + ',' + right(yourfield,3)
when len(yourfield) = 7 then left(yourfield,1) + ',' + substring(convert(varchar,yourfield),2,3) + ',' + right(yourfield,3)
when len(yourfield) = 8 then left(yourfield,2) + ',' + substring(convert(varchar,yourfield),3,3) + ',' + right(yourfield,3)
when len(yourfield) = 9 then left(yourfield,3) + ',' + substring(convert(varchar,yourfield),4,3) + ',' + right(yourfield,3)
when len(yourfield) = 10 then left(yourfield,1) + ',' + substring(convert(varchar,yourfield),2,3) + ',' + substring(convert(varchar,yourfield),5,3) + ',' + right(yourfield,3)
else convert(varchar,yourfield)
end)

BTW you obviously dont need the first line, I left that in from my test query

HTH

Matt

Brighton, UK

 

[pascalsql]

We probably can do better using the seldom-used function reverse. Something like:
reverse the string
add a column every 3 character until the end of the string
reverse again

But this job is probably best done by the client interface

 

[fluteplr]

I agree with pascalsql but for those occasions where your Really need to do it. Here is a user defined function that will format the number.

CREATE FUNCTION dbo.fnformatnumber (@number float, @decimal int =0)
RETURNS varchar(30) AS
BEGIN
declare @retval varchar(30),
@pos int

if @number is null
begin
set @number = 0
end

set @retval = rtrim(ltrim(str(round(abs(@number),@decimal),20,@decimal)))

set @pos=charindex('.',@retval)
If @pos=0
Set @pos=len(@retval)-2
Else
Set @pos=@pos-3

While @pos>1
Begin
Set @retval=stuff(@retval,@pos,0,',')
Set @pos=@pos-3
End


if @number < 0
begin
set @retval = '('+@retval+')'
end

return @retval
END

 

[sivi]

Thanks Matt, pascalsql and fluteplr.

I have gone for Matt's solution as I have to do this in SQL Server.

Sivi