Numeric to Character
-
hi
is there any function through which i convert Numeric to Character
Ex :100 to hundred
Thanks
-
I don't remember where I got this or who wrote it, or I would give due credit. Steve...
CREATE function NumbersWords(@s VARCHAR(50))
--English words for numbers
returns VARCHAR(1024)
as
BEGIN
DECLARE @a char(1),@b char(1),@c char(1),@i int, @j-2 int, @result VARCHAR(1024), @orlen int
SET @orlen=LEN(@s)
IF LEN(@s) % 3>0
IF LEN(@s) % 3>0
SET @i=1 SET @result=''
IF LEN(@s)=1 AND @s-2='0'
SET @result='zero'
WHILE @i<=LEN(@s)
BEGIN
SET @j-2=LEN(@s)-@i+1
SET @a=substring(@s,@j,1)
SET @b-2=substring(@s,@j-1,1)
SET @c=substring(@s,@j-2,1)
if isnumeric(@a)=1
BEGIN
SET @result=case
WHEN (@i-1=3) AND (@c+@b+@a<>'000') THEN 'thousand'
WHEN (@i-1=6) AND (@c+@b+@a<>'000') THEN 'million'
WHEN (@i-1=9) AND (@c+@b+@a<>'000') THEN 'billion'
WHEN (@i-1=12) AND (@c+@b+@a<>'000') THEN 'trillion'
WHEN (@i-1=15) AND (@c+@b+@a<>'000') THEN 'quadrillion'
WHEN (@i-1=18) AND (@c+@b+@a<>'000') THEN 'quintillion'
WHEN (@i-1=21) AND (@c+@b+@a<>'000') THEN 'sextillion'
WHEN (@i-1=24) AND (@c+@b+@a<>'000') THEN 'septillion'
WHEN (@i-1=27) AND (@c+@b+@a<>'000') THEN 'octillion'
WHEN (@i-1=30) AND (@c+@b+@a<>'000') THEN 'nonillion'
WHEN (@i-1=33) AND (@c+@b+@a<>'000') THEN 'decillion'
WHEN (@i-1=36) AND (@c+@b+@a<>'000') THEN 'undecillion'
WHEN (@i-1=39) AND (@c+@b+@a<>'000') THEN 'duodecillion'
WHEN (@i-1=42) AND (@c+@b+@a<>'000') THEN 'tredecillion'
WHEN (@i-1=45) AND (@c+@b+@a<>'000') THEN 'quattuordecillion'
WHEN (@i-1=48) AND (@c+@b+@a<>'000') THEN 'quindecillion'
WHEN (@i-1=51) AND (@c+@b+@a<>'000') THEN 'sexdecillion'
WHEN (@i-1=54) AND (@c+@b+@a<>'000') THEN 'septendecillion'
WHEN (@i-1=57) AND (@c+@b+@a<>'000') THEN 'octodecillion'
WHEN (@i-1=60) THEN 'novemdecillion'
ELSE ''
END+' '+ @result
SET @result=case @a
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'
ELSE ''
END+' '+ @result
if (isnumeric(@b)=1 )AND (@b!='0')
IF @b-2='1'
SET @result=case @a
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'
ELSE ''
END+' '+ @result
ELSE
SET @result=case @b-2
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'
ELSE ''
END+' '+ @result
END
if (isnumeric(@c)=1)AND (@c!='0')
SET @result=case @c
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'
ELSE ''
END+' hundred'+' '+ @result
SET @i=@i+3
END
SET @result=LTRIM(RTRIM(@result))
return @result
END
-
It works great! Just on note, in the hundreds, forty is spelled wrong.
SET @result=case @b-2
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'
ELSE ''
END+' '+ @result
END
----------------
Jim P.A little bit of this and a little byte of that can cause bloatware.
-
Sure is! Thanks!
Steve
-
Thanks for your timely support,it really works..
-
No problem!
I kept it because I thought it was cool. I didn't know if I would ever have a use for it. But if you can use it, its been worth keeping!
Steve
-
this is really great, i made a com component for a function like this.
i never even thought its possible in sql query
<a href="http://www.websolsoftware.com"> For IT jobs click here</a>
*Sukhoi*[font="Arial Narrow"][/font]
-
OK, whats REALLY amazing, is that in the single script file that I downloaded is:
Numeric to character functions for English, Spanish and Portugese
Numeric to character stored procedures for English, Spanish and Portugese
Text to SPEECH (yup, really!!!) Stored Procedure
all Transact SQL (the text to speech uses OA)
Steve
Viewing 8 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply