UDF to convert currency to English string

[TJRTech]

It's been a while since I posted or helped out some folks here, but I am back with a question.

I have a website I am working on that has to display a currency value as stored in the database as a text string. It has to optionally display cents if available, and deal with values into the 100s of millions of dollars.

I know I could do this in ASP or other middle-ware, but I was hoping to write a user-defined function that given a float returns the string representation as described, for example:

Input:
128134.50

Output:
One Hundred Twenty Eight Thousand, One Hundred Thirty Four and 50/100.

Here is what I have for a UDF, and it seems to work, I am just wondering if I got all the various cases worked out, and if their might be some easier or better way to do this?

Sorry for the formatting of the code; please cut and paste into Query Analyzer to create the UDF and to view the code better; thanks!

-- Returns a text string that describes an amount
CREATE FUNCTION GetAmountString( @amount float )
RETURNS varchar(100)
AS
BEGIN 

declare @orig_amt float
declare @factor float
declare @str_amount varchar(100)
declare @mod int

set @orig_amt = @amount
set @factor = 1000000 -- Supports up to 999,999,999.99 (1 penny shy of 1 billion)
set @str_amount = ''

-- Loop through working on Millions, Thousands, Hundreds and Pennies.
while @amount > 0.00
 begin
    -- If Pennies
    if @amount > 0.0 and @amount < 1.0
       begin
           if @str_amount<>'' set @str_amount = @str_amount + ' and '
           set @str_amount = @str_amount + convert(varchar, convert(int, @amount*100)) + '/100'
           set @amount = 0.00
       end
    else
       begin	       
            -- Get modulus amount
	    set @mod = convert(int, @amount) / @factor
            -- If Twenty or less
	    if @mod <= 20
	       begin
	          select @str_amount = @str_amount + ' ' + NumberString from NumberString where NumberValue = @mod
	       end
            -- If greater than twenty but less than hundred (parse 10s, 1s)
	    if @mod > 20 and @mod <=99
	       begin
	          select @str_amount = @str_amount + ' ' + NumberString from NumberString where NumberValue = (@mod / 10)*10
	          select @str_amount = @str_amount + ' ' + NumberString from NumberString where NumberValue = @mod % 10
	       end
            -- If greater than one hundres (parse 100s, 10s, 1s)
	    if @mod > 99 begin
	          select @str_amount = @str_amount + ' ' + NumberString + ' Hundred' from NumberString where NumberValue = @mod / 100
                  -- Tens and Ones could be less twenty or less, then parse as teens or single digit
                  if @mod % 100 <= 20
                    begin
                       select @str_amount = @str_amount + ' ' + NumberString from NumberString where NumberValue = @mod%100
                    end
                  else
                    -- else parse and tens and single digit
                    begin
             	        select @str_amount = @str_amount + ' ' + NumberString from NumberString where NumberValue = (@mod%100 / 10)*10
	          	select @str_amount = @str_amount + ' ' + NumberString from NumberString where NumberValue = @mod % 10       
                    end
	    end
            -- Add in Million or Thousand keyword
            if @factor > 1 and @str_amount<> ''
              begin
                  select @str_amount = @str_amount + ' ' + NumberString from NumberString where NumberValue = @factor
              end
	    select @amount = @amount - @factor * @mod
            -- Add in comma if additional monies follow and Million or Thousand preceeded
            if @str_amount<> '' and @amount>=1.0 and @factor>1 select @str_amount = @str_amount + ','
	    set @factor = @factor / 1000
      end
end
if @str_amount='' set @str_amount='Zero'
set @str_amount = replace(ltrim(@str_amount), '  ', ' ')

return(@str_amount)

END

Thanks,
TJR

 

[TJRTech]

Almost forgot, the UDF uses the following table to lookup words given numbers:

-- *** NumberString ***
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[NumberString]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[NumberString]
GO

CREATE TABLE [dbo].[NumberString] (
        NumberValue integer NOT NULL,
        NumberString varchar(100) NOT NULL
	CONSTRAINT [PK_NumberString] PRIMARY KEY CLUSTERED 
	(
		[NumberValue]
	)  ON [PRIMARY] 
)

-- Insert values
insert into NumberString (NumberValue, NumberString) values(0, '')
insert into NumberString (NumberValue, NumberString) values(1, 'One')
insert into NumberString (NumberValue, NumberString) values(2, 'Two')
insert into NumberString (NumberValue, NumberString) values(3, 'Three')
insert into NumberString (NumberValue, NumberString) values(4, 'Four')
insert into NumberString (NumberValue, NumberString) values(5, 'Five')
insert into NumberString (NumberValue, NumberString) values(6, 'Six')
insert into NumberString (NumberValue, NumberString) values(7, 'Seven')
insert into NumberString (NumberValue, NumberString) values(8, 'Eight')
insert into NumberString (NumberValue, NumberString) values(9, 'Nine')
insert into NumberString (NumberValue, NumberString) values(10, 'Ten')
insert into NumberString (NumberValue, NumberString) values(11, 'Eleven')
insert into NumberString (NumberValue, NumberString) values(12, 'Twelve')
insert into NumberString (NumberValue, NumberString) values(13, 'Thirteen')
insert into NumberString (NumberValue, NumberString) values(14, 'Fourteen')
insert into NumberString (NumberValue, NumberString) values(15, 'Fifteen')
insert into NumberString (NumberValue, NumberString) values(16, 'Sixteen')
insert into NumberString (NumberValue, NumberString) values(17, 'Seventeen')
insert into NumberString (NumberValue, NumberString) values(18, 'Eighteen')
insert into NumberString (NumberValue, NumberString) values(19, 'Nineteen')
insert into NumberString (NumberValue, NumberString) values(20, 'Twenty')
insert into NumberString (NumberValue, NumberString) values(30, 'Thirty')
insert into NumberString (NumberValue, NumberString) values(40, 'Forty')
insert into NumberString (NumberValue, NumberString) values(50, 'Fifty')
insert into NumberString (NumberValue, NumberString) values(60, 'Sixty')
insert into NumberString (NumberValue, NumberString) values(70, 'Seventy')
insert into NumberString (NumberValue, NumberString) values(80, 'Eighty')
insert into NumberString (NumberValue, NumberString) values(90, 'Ninety')
insert into NumberString (NumberValue, NumberString) values(100, 'One Hundred')
insert into NumberString (NumberValue, NumberString) values(1000, 'Thousand')
insert into NumberString (NumberValue, NumberString) values(1000000, 'Million')

 

[TJRTech]

I know it was a lot to read there, and asking a little that people cut/paste to examine code, but does anyone have any comments whatsoever?

Thanks.

TJR