Commas Needed 1

[fluteplr]

For reasons too boring to explain I need to create a user defined function that will return a number converted to a varchar with commas and decimals in the appropriate places.


As in:

select dbo.formatnumber(1234567.123)

should return a varchar containing '1,234,567.123'

If there is an obvious way to do this I apologize in advance.


I am running SQL Server 2000

 

[tlbroadbent]

It is very simple if you always want two decimal points. The Convert function will add the commas. However, it is a little more challenging if the numbers will not always be fixed at two decimals.

Using the Convert function:

Select Convert(varchar(20), Convert(money, ColName), 1)

Creating a UDF: This should get you started.

CREATE FUNCTION dbo.fn_ConvertNumToChar (@num decimal(18,4))
RETURNS varchar(30)
AS
BEGIN

--Usage Select dbo.fn_ConvertNumToChar(1234567.123)

Declare @pos int, @char varchar(18)

set @char=convert(varchar(18), @num)
set @pos=charindex('.',@char)
If @pos=0
Set @pos=len(@char)-2
Else
Set @pos=@pos-3

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

Return(@char)
End Terry L. Broadbent
FAQ183-874 contains tips for posting questions in these forums.
NOTE: Reference to the FAQ is not directed at any individual.

 

[fluteplr]

Thanks, Terry.

For those interested here is the complete function. Note it is designed to return negative numbers in accounting format. (No minus sign, with parens.) The second parameter specified the number of decimals.

As in:

dbo.fnformatnumber(-1234567.123) will return '(1,234,567.123)'

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

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