SQL Function To Add Commas to Numbers

[jw2000]

I have this working in VB. How can I create a SQL Function to do something similar? I am not good with SQL syntax.

ie.
123 -> 123
1000 -> 1,000
10000 -> 10,000
200000 -> 200,000
5000000 -> 5,000,000
100000000 -> 100,000,000


function Make_Comma_Number(Data)

HowMany = Len(Data)

If HowMany < 4 Then
Make_Comma_Number = Data
Else

'Add in commas----------------------
For i = 1 To HowMany
NewData = Mid(Data, i, 1) & NewData
Next 'i

For a = 1 To HowMany
Counter = Counter + 1

If Counter = 4 Then
DataOf = Mid(NewData, a, 1) & "," & DataOf
Counter = 1
Else
DataOf = Mid(NewData, a, 1) & DataOf
End If

Next 'a
'-----------------------------------

Make_Comma_Number = DataOf

End If

end function

 

[SajidAttar]

Create Function dbo.FormatNumber (@Value int)
returns varchar(255) as 
begin
declare @RValue Varchar(255), @TValue varchar(255)



set @TValue = convert(varchar,@Value)

while @TValue <> ''
begin
	if Len(@TValue) > 3 
	begin
		set @RValue = left(@TValue,3) + isnull (',' + @RValue, '')
		set @TValue = right(@TValue, len(@TValue) - 3)
	end
	else
	begin
		set @RValue = @TValue + isnull (',' + @RValue, '')
		set @TVAlue = ''
	end
end

return(@RValue)

end

/* Use function like this
    Select FormatNumber (123456789)
or 
    Select FormatNumber(myINTColumn) from myTable
*/

Regards,


"There are no secrets to success. It is the result of preparation, hard work, and learning from failure." -- Colin Powell

 

[jbenson001]

Check this thread:
thread183-1152670

Jim

 

[earthandfire]

This is covered in:

thread183-1152670

parsename(convert(varchar(50),convert(money,100000000,1), 1),2)

Hope this helps.

 

[jw2000]

SajidAttar,

Doesn't work ...

Select dbo.FormatNumber (123456789) -> 789,456,123



Create Function FormatNumber (@Value int) returns varchar(255) as
begin
declare @RValue Varchar(255), @TValue varchar(255)

set @TValue = convert(varchar,@Value)

while @TValue <> ''
begin
if Len(@TValue) > 3
begin
set @RValue = left(@TValue,3) + isnull (',' + @RValue, '')
set @TValue = right(@TValue, len(@TValue) - 3)
end
else
begin
set @RValue = @TValue + isnull (',' + @RValue, '')
set @TVAlue = ''
end
end

return @RValue
end

 

[earthandfire]

Sorry, jbenson001. I typed this up and then couldn't get back in here for a while. Posted as soon as I could, but didn't check first.

 

[jw2000]

Select Replace(Convert(VarChar(10), Convert(Money, 123456789), 1), '.00', '')

returns -> There is insufficient result space to convert a money value to varchar.


select parsename(convert(varchar(10), convert(money, 123456789), 1), 2)

returns -> There is insufficient result space to convert a money value to varchar.

 

[jw2000]

There's a rounding problem with decimals as well:

select parsename(convert(varchar(50), convert(money, 1000000000.51), 1), 2)


Any suggestions?

 

[SajidAttar]

Here is corrected one . . .

Create  Function dbo.FormatNumber (@Value int)
returns varchar(255) as 
begin
declare @RValue Varchar(255), @TValue varchar(255)



set @TValue = convert(varchar,@Value)

while @TValue <> ''
begin
	if Len(@TValue) > 3 
	begin
		set @RValue =  isnull ( @RValue + ',', '') + left(@TValue,3)
		set @TValue = right(@TValue, len(@TValue) - 3)
	end
	else
	begin
		set @RValue =  isnull ( @RValue + ',', '') + left(@TValue,3)
		set @TVAlue = ''
	end
end

return(@RValue)

end

Regards,


"There are no secrets to success. It is the result of preparation, hard work, and learning from failure." -- Colin Powell

 

[jbenson001]

No apologies necessary

 

[earthandfire]

Thanks jbenson001, I'm pretty new on this forum and I don't want toi upset the regulars (I want to learn form them )


jw2000

try this:

declare @val decimal(30,8)
set @val = 100000000.49999999
if convert(int,left(parsename(@val,1),1)) >= 5
begin
 set @val = parsename(@val,2) + 1
end
else
begin
 set @val = parsename(@val, 2)
end
select parsename(convert(varchar(50),convert(money,@val,1), 1), 2)

Hope this helps.

 

[jbenson001]

No problem and you will learn lots here. I do every day..

And by the way, great piece of code. I will keep it for furture refernce

 

[jw2000]

SajidAttar, code still has some problems:

Select dbo.FormatNumber (10000) -> 100,00
Select dbo.FormatNumber (5000000) -> 500,000,0
Select dbo.FormatNumber (100000000) -> 100,000,000
Select dbo.FormatNumber (100000000.51) -> 100,000,000


Earthandfire, I tried different values for @val but the code breaks for 5000000000.51

declare @val decimal(30,8)
--set @val = 100000000.49999999
--set @val = 100000000.50
set @val = 5000000000.51
if convert(int,left(parsename(@val,1),1)) >= 5
begin
set @val = parsename(@val,2) + 1
end
else
begin
set @val = parsename(@val, 2)
end

select parsename(convert(varchar(50),convert(money,@val,1), 1), 2)

-> The conversion of the nvarchar value '5000000000' overflowed an int column. Maximum integer value exceeded.

 

[earthandfire]

OK, how about this:

declare @val decimal(38,8)
set @val = 5000000000.51
if convert(int,left(parsename(@val,1),1)) >= 5
begin
 set @val = parsename(@val,2) + [b]convert(bigint,1)[/b]
end
else
begin
 set @val = parsename(@val, 2)
end
select parsename(convert(varchar(50),convert(money,@val,1), 1), 2)

 

[earthandfire]

I don't know how large your figures are but from BOL:


money

Monetary data values from -2^63 (-922,337,203,685,477.5808) through 2^63 - 1 (+922,337,203,685,477.5807), with accuracy to a ten-thousandth of a monetary unit.


bigint

Integer (whole number) data from -2^63 (-9223372036854775808) through 2^63-1 (9223372036854775807).

Hope this helps.

 

[jw2000]

Got it ... thank you Earthandfire!

 

[earthandfire]

That means that my example will fail on anything larger than:

declare @val decimal(38,8)

set @val = [b]922337203685477.4999[/b]  --This works
--set @val = [b]922337203685477.5[/b]   --This will fail
if convert(int,left(parsename(@val,1),1)) >= 5
begin
 set @val = parsename(@val,2) + convert(bigint,1)
end
else
begin
 set @val = parsename(@val, 2)
end
select parsename(convert(varchar(50),convert(money,@val,1), 1), 2)

Hope this helps.