$123 --> One Hundred Twenty-three ...

Question

Post reply

$123 --> One Hundred Twenty-three ...

Bill Nye

Hall of Fame

Points: 3248
More actions
August 13, 2003 at 12:10 pm

#80849

Does anyone have any script or know a way that I can convert $amount to words, as written on a check?
Before I start writing a bunch of code I thought I would ask, since there's probably already a solution out there somewhere.
Thanks.

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply

Frank Kalis SSC Guru Points: 111183 More actions · Answer 1

Hi BillNye101,

quote:
Does anyone have any script or know a way that I can convert $amount to words, as written on a check?

I once thought that was a nice gimmick, so I wrote this



DECLARE @test float

SET @test = 123



BEGIN

DECLARE @i int

DECLARE @tmp char(1)

DECLARE @lpsz VARCHAR(20)

DECLARE @RetVal VARCHAR(255)



SELECT @lpsz=CONVERT(varchar(20), @test)

SELECT @i=LEN(@lpsz)

SELECT @RetVal=''

WHILE (@i>0)

BEGIN

SELECT @tmp=(SUBSTRING(@lpsz,@i,1))

IF ((LEN(@lpsz)-@i) % 3)=1

IF @tmp='1'

SELECT @RetVal=CASE (SUBSTRING(@lpsz,@i+1,1))

WHEN '0' THEN 'Ten'

WHEN '1' THEN 'Eleven'

WHEN '2' THEN 'Twelve'

WHEN '3' THEN 'Thirteen'

WHEN '4' THEN 'Fourteen'

WHEN '5' THEN 'Fifteen'

WHEN '6' THEN 'Sixteen'

WHEN '7' THEN 'Seventeen'

WHEN '8' THEN 'Eighteen'

WHEN '9' THEN 'Nineteen'

END+' '+CASE

WHEN ((LEN(@lpsz)-@i)=4) THEN 'Thousand '

WHEN ((LEN(@lpsz)-@i)=7) THEN 'Million '

WHEN ((LEN(@lpsz)-@i)=10) THEN 'Billion '

WHEN ((LEN(@lpsz)-@i)=13) THEN 'Trillion '

ELSE ''

END+@RetVal

ELSE

BEGIN

SELECT @RetVal=CASE (SUBSTRING(@lpsz,@i+1,1))

WHEN '0' THEN ''

WHEN '1' THEN 'One'

WHEN '2' THEN 'Two'

WHEN '3' THEN 'Three'

WHEN '4' THEN 'Four'

WHEN '5' THEN 'Five'

WHEN '6' THEN 'Six'

WHEN '7' THEN 'Seven'

WHEN '8' THEN 'Eight'

WHEN '9' THEN 'Nine'

END+' '+ CASE

WHEN ((LEN(@lpsz)-@i)=4) THEN 'Thousand '

WHEN ((LEN(@lpsz)-@i)=7) THEN 'Million '

WHEN ((LEN(@lpsz)-@i)=10) THEN 'Billion '

WHEN ((LEN(@lpsz)-@i)=13) THEN 'Trillion '

ELSE ''

END+@RetVal

SELECT @RetVal=CASE @tmp

WHEN '0' THEN ''

WHEN '1' THEN 'Ten'

WHEN '2' THEN 'Twenty'

WHEN '3' THEN 'Thirty'

WHEN '4' THEN 'Fourty'

WHEN '5' THEN 'Fifty'

WHEN '6' THEN 'Sixty'

WHEN '7' THEN 'Seventy'

WHEN '8' THEN 'Eighty'

WHEN '9' THEN 'Ninety'

END+' '+@RetVal

END

IF (((LEN(@lpsz)-@i) % 3)=2) OR (((LEN(@lpsz)-@i) % 3)=0) AND (@i=1)

BEGIN

SELECT @RetVal=CASE @tmp

WHEN '0' THEN ''

WHEN '1' THEN 'One'

WHEN '2' THEN 'Two'

WHEN '3' THEN 'Three'

WHEN '4' THEN 'Four'

WHEN '5' THEN 'Five'

WHEN '6' THEN 'Six'

WHEN '7' THEN 'Seven'

WHEN '8' THEN 'Eight'

WHEN '9' THEN 'Nine'

END +' '+CASE

WHEN (@lpsz='0') THEN 'Zero'

WHEN (@tmp<>'0')AND( ((LEN(@lpsz)-@i) % 3)=2) THEN 'Hundred '

ELSE ''

END + CASE

WHEN ((LEN(@lpsz)-@i)=3) THEN 'Thousand '

WHEN ((LEN(@lpsz)-@i)=6) THEN 'Million '

WHEN ((LEN(@lpsz)-@i)=9) THEN 'Billion '

WHEN ((LEN(@lpsz)-@i)=12) THEN 'Trillion '

ELSE ''

END+ @RetVal

END

SELECT @i=@i-1

END

PRINT REPLACE(@RetVal,'  ',' ')

END

if you are on SQL2K you might want to consider putting this into a function. In my example I chose float as variable type. Could be any numeric type. Please note, this doesn't work with decimal places. I never tried to figure it out why

quote:
Before I start writing a bunch of code I thought I would ask, since there's probably already a solution out there somewhere.

This is always a smart solution, before reinventing the wheel.

Take a close look at it, for I have translated from german to english. It is likely that there are typos.

HTH

Cheers,

Frank

--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]

Bill Nye Hall of Fame Points: 3248 More actions · Answer 2

Excellent!

Thank you.

Bill.

Edited by - billnye101 on 08/14/2003 3:23:26 PM

Bill Nye Hall of Fame Points: 3248 More actions · Answer 3

I finally got back to this project and made some modifications: added cents and added hyphens where approriate.

 CREATE FUNCTION fn$_to_Dollars

(@Cash float)

 

RETURNS varchar(100)

AS

BEGIN

-- a5xo3z1 / Frank 

-- SQL Server Guru - SQLServercentral.com

-- Germany 

-- Posted - 08/14/2003 :  12:17:03 AM   

-- Modified By Bill Nye 20030904 to work w/decimals and include hyphens       

DECLARE @cents as tinyint, @fill as tinyint



set @Cents=right(cast(round(@Cash*100,0) as bigint),2)



BEGIN

DECLARE @i int

DECLARE @tmp char(1)

DECLARE @lpsz VARCHAR(20)

DECLARE @RetVal VARCHAR(255)

DECLARE @hyphen char(1), @flag char(1) 





SELECT @lpsz=CONVERT(varchar(20), cast(@Cash as bigint))  --cast added, otherwise >1mm converts to exponent and won't work

SELECT @i=LEN(@lpsz)

SELECT @RetVal=''

WHILE (@i>0)

BEGIN



SELECT @tmp=(SUBSTRING(@lpsz,@i,1))



IF ((LEN(@lpsz)-@i) % 3)=1



IF @tmp='1'

SELECT @RetVal=CASE (SUBSTRING(@lpsz,@i+1,1))

WHEN '0' THEN 'Ten'

WHEN '1' THEN 'Eleven'

WHEN '2' THEN 'Twelve'

WHEN '3' THEN 'Thirteen'

WHEN '4' THEN 'Fourteen'

WHEN '5' THEN 'Fifteen'

WHEN '6' THEN 'Sixteen'

WHEN '7' THEN 'Seventeen'

WHEN '8' THEN 'Eighteen'

WHEN '9' THEN 'Nineteen'

END+' '+CASE

WHEN ((LEN(@lpsz)-@i)=4) THEN 'Thousand '

WHEN ((LEN(@lpsz)-@i)=7) THEN 'Million '

WHEN ((LEN(@lpsz)-@i)=10) THEN 'Billion '

WHEN ((LEN(@lpsz)-@i)=13) THEN 'Trillion '

ELSE ''

END+@RetVal

ELSE

BEGIN

SELECT @RetVal=CASE (SUBSTRING(@lpsz,@i+1,1))

WHEN '0' THEN ''

WHEN '1' THEN 'One'

WHEN '2' THEN 'Two'

WHEN '3' THEN 'Three'

WHEN '4' THEN 'Four'

WHEN '5' THEN 'Five'

WHEN '6' THEN 'Six'

WHEN '7' THEN 'Seven'

WHEN '8' THEN 'Eight'

WHEN '9' THEN 'Nine'

END+' '+ CASE

WHEN ((LEN(@lpsz)-@i)=4) THEN 'Thousand '

WHEN ((LEN(@lpsz)-@i)=7) THEN 'Million '

WHEN ((LEN(@lpsz)-@i)=10) THEN 'Billion '

WHEN ((LEN(@lpsz)-@i)=13) THEN 'Trillion '

ELSE ''

END+@RetVal



--***************  Added to include hyphens ie 53=fifty-three

begin

SELECT @hyphen=case(SUBSTRING(@lpsz,@i+1,1))

WHEN '0' THEN ' '

ELSE '-'

END

end

--***************

SELECT @RetVal=CASE @tmp

WHEN '0' THEN ''

WHEN '1' THEN 'Ten'

WHEN '2' THEN 'Twenty'

WHEN '3' THEN 'Thirty'

WHEN '4' THEN 'Forty'

WHEN '5' THEN 'Fifty'

WHEN '6' THEN 'Sixty'

WHEN '7' THEN 'Seventy'

WHEN '8' THEN 'Eighty'

WHEN '9' THEN 'Ninety'

END+@hyphen+@RetVal

END



IF (((LEN(@lpsz)-@i) % 3)=2) OR (((LEN(@lpsz)-@i) % 3)=0) AND (@i=1) 

BEGIN SELECT @RetVal=CASE @tmp

WHEN '0' THEN ''

WHEN '1' THEN 'One'

WHEN '2' THEN 'Two'

WHEN '3' THEN 'Three'

WHEN '4' THEN 'Four'

WHEN '5' THEN 'Five'

WHEN '6' THEN 'Six'

WHEN '7' THEN 'Seven'

WHEN '8' THEN 'Eight'

WHEN '9' THEN 'Nine'

END +' '+CASE

WHEN (@lpsz='0') THEN 'Zero'

WHEN (@tmp<>'0')AND( ((LEN(@lpsz)-@i) % 3)=2) THEN 'Hundred '

ELSE ''

END + CASE

WHEN ((LEN(@lpsz)-@i)=3) THEN 'Thousand '

WHEN ((LEN(@lpsz)-@i)=6) THEN 'Million '

WHEN ((LEN(@lpsz)-@i)=9) THEN 'Billion '

WHEN ((LEN(@lpsz)-@i)=12) THEN 'Trillion '

ELSE ''

END+ @RetVal

END

SELECT @i=@i-1



END

-- Added 2 extra replaces to get rid of occasional extra space and hyphen when like 109, otherwise it shows "one hundred -nine" 

SET @RetVal=REPLACE(REPLACE(REPLACE(@RetVal,'  ',' '),'  ',' '),' -',' ')



SET @RetVal=@RetVal+'and ' +cast(@cents as char(2)) +'/100 '



SELECT @fill= case sign(84-len(@RetVal)) -- Assumes length of 85 for amount field on check 

WHEN -1.0 Then 0 -- otherwise, nothing shows

 Else 84-(len(@RetVal))

END



RETURN @RetVal+Replicate('*',@fill)

END



END

GO

Thanks again, Frank.

Antares686 SSC Guru Points: 125444 More actions · Answer 4

Here is a script I wrote a long while back that does it and should cover all possiblities including the difference between hyphenation of things like Twenty-Five as opposed to Twenty Five.

http://www.sqlservercentral.com/scripts/contributions/259.asp

Frank Kalis SSC Guru Points: 111183 More actions · Answer 5

quote:
Here is a script I wrote a long while back that does it and should cover all possiblities including the difference between hyphenation of things like Twenty-Five as opposed to Twenty Five.
http://www.sqlservercentral.com/scripts/contributions/259.asp

actually I would love to see the day when you can't top it

Frank

--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]

Antares686 SSC Guru Points: 125444 More actions · Answer 6

Thanks, but topping things really isn't my goal. I can see your code does a quite lovely job. Just offering ("a better", just kidding ) another option as far as I am concerned.

Frank Kalis SSC Guru Points: 111183 More actions · Answer 7

it wasn't meant seriously!

and it is definitely always good to see someone else approach!

and why are you already at work?

suddenly see the need to do some extratime

what about working on weekends?

Frank

--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]

Antares686 SSC Guru Points: 125444 More actions · Answer 8

quote:
and why are you already at work?
suddenly see the need to do some extratime
what about working on weekends?

I am Eastern Time Zone and have worked it out so I go in around 5 AM and get off at 1 PM so I can pick up my oldest son from school.

Great thing is I can telecommute as well (which I am doing now from my P4 2.6 HT machine which far outclasses my POS at work, it's new so I gotta brag).

And I don't have a fixed schedual so I can work a few hours disappear for a bit and return later just as long as I get 8 in a day as far as the boss is concerned.