SQL Server Performance

Simple Recursive fn for convert Number to Word

Discussion in 'Contribute Your SQL Server Scripts' started by patel_mayur, Sep 22, 2004.

  1. patel_mayur New Member

    <pre>Create Function No2Word ( @StrNo Varchar(100))<br />Returns Varchar(100)<br />As<br />Begin<br />Declare @StrRet as Varchar(100)<br />Declare @Len as Int<br /><br />Select @Len = Len(@StrNo)<br /><br />If @Len = 1 <br />Begin<br />Select@StrRet = Case @StrNo <br />When '1' Then 'One'<br />When '2' Then 'Two'<br />When '3' Then 'Three'<br />When '4' Then 'Four'<br />When '5' Then 'Five'<br />When '6' Then 'Six'<br />When '7' Then 'Seven'<br />When '8' Then 'Eight'<br />When '9' Then 'Nine'<br />Else ''<br />End<br />End<br />If @Len = 2<br />Begin<br />Select@StrRet = Case <br />When @StrNo = '10' Then IsNull(@StrRet,'') + 'Ten'<br />When @StrNo = '11' Then IsNull(@StrRet,'') + 'Eleven'<br />When @StrNo = '12' Then IsNull(@StrRet,'') + 'Twelve'<br />When @StrNo = '13' Then IsNull(@StrRet,'') + 'Thirteen'<br />When @StrNo = '14' Then IsNull(@StrRet,'') + 'Fourteen'<br />When @StrNo = '15' Then IsNull(@StrRet,'') + 'Fifteen'<br />When @StrNo = '16' Then IsNull(@StrRet,'') + 'Sixteen'<br />When @StrNo = '17' Then IsNull(@StrRet,'') + 'Seventeen'<br />When @StrNo = '18' Then IsNull(@StrRet,'') + 'Eighteen'<br />When @StrNo = '19' Then IsNull(@StrRet,'') + 'Ninteen'<br />Else IsNull(@StrRet,'')<br />End<br /><br />Select@StrRet = Case <br />When Substring(@StrNo,1,1) = '2' Then IsNull(@StrRet,'') + 'Twenty '<br />When Substring(@StrNo,1,1) = '3' Then IsNull(@StrRet,'') + 'Thirty '<br />When Substring(@StrNo,1,1) = '4' Then IsNull(@StrRet,'') + 'Fourty '<br />When Substring(@StrNo,1,1) = '5' Then IsNull(@StrRet,'') + 'Fifty '<br />When Substring(@StrNo,1,1) = '6' Then IsNull(@StrRet,'') + 'Sixty '<br />When Substring(@StrNo,1,1) = '7' Then IsNull(@StrRet,'') + 'Seventy '<br />When Substring(@StrNo,1,1) = '8' Then IsNull(@StrRet,'') + 'Eighty '<br />When Substring(@StrNo,1,1) = '9' Then IsNull(@StrRet,'') + 'Ninty '<br />Else<br />@StrRet<br />End<br /><br />If Convert(Numeric,Substring(@StrNo,1,1)) &gt; 1<br />Begin<br />Select @StrRet = @StrRet + dbo.No2Word(Substring(@StrNo,2,1))<br />End<br /><br />If Substring(@StrNo,1,1) = '0' And Convert(Numeric,Substring(@StrNo,2,1)) &gt; 0<br />Begin<br />Select @StrRet = @StrRet + dbo.No2Word(Substring(@StrNo,2,1))<br />End<br />End<br />If @Len = 3<br />Begin<br />If Convert(Numeric,SubString(@StrNo,1,1)) &gt; 0<br />Select @StrRet = Isnull(@StrRet,'') + dbo.No2Word(Substring(@StrNo,1,1)) + ' Hundred '<br />Select @StrRet = Isnull(@StrRet,'') + dbo.No2Word(Substring(@StrNo,2,2))<br />End<br />If @Len = 4<br />Begin<br />If Convert(Numeric,SubString(@StrNo,1,1)) &gt; 0<br />Select @StrRet = Isnull(@StrRet,'') + dbo.No2Word(Substring(@StrNo,1,1)) + ' Thousand '<br />Select @StrRet = Isnull(@StrRet,'') + dbo.No2Word(Substring(@StrNo,2,3))<br />End<br />If @Len = 5<br />Begin<br />If Convert(Numeric,SubString(@StrNo,1,2)) &gt; 0<br />Select @StrRet = Isnull(@StrRet,'') + dbo.No2Word(Substring(@StrNo,1,2)) + ' Thousand '<br />Select @StrRet = Isnull(@StrRet,'') + dbo.No2Word(Substring(@StrNo,3,3))<br />End<br />If @Len = 6<br />Begin<br />If Convert(Numeric,SubString(@StrNo,1,1)) &gt; 0<br />Select @StrRet = Isnull(@StrRet,'') + dbo.No2Word(Substring(@StrNo,1,1)) + ' Lack '<br />Select @StrRet = Isnull(@StrRet,'') + dbo.No2Word(Substring(@StrNo,2,5))<br />End<br />If @Len = 7<br />Begin<br />If Convert(Numeric,SubString(@StrNo,1,2)) &gt; 0<br />Select @StrRet = Isnull(@StrRet,'') + dbo.No2Word(Substring(@StrNo,1,2)) + ' Lack '<br />Select @StrRet = Isnull(@StrRet,'') + dbo.No2Word(Substring(@StrNo,3,5))<br />End<br />If @Len = 8<br />Begin<br />If Convert(Numeric,SubString(@StrNo,1,1)) &gt; 0<br />Select @StrRet = Isnull(@StrRet,'') + dbo.No2Word(Substring(@StrNo,1,1)) + ' Crore '<br />Select @StrRet = Isnull(@StrRet,'') + dbo.No2Word(Substring(@StrNo,2,7))<br />End<br />If @Len = 9<br />Begin<br />If Convert(Numeric,SubString(@StrNo,1,2)) &gt; 0<br />Select @StrRet = Isnull(@StrRet,'') + dbo.No2Word(Substring(@StrNo,1,2)) + ' Crore '<br />Select @StrRet = Isnull(@StrRet,'') + dbo.No2Word(Substring(@StrNo,3,<img src='/community/emoticons/emotion-11.gif' alt='8)' />)<br />End<br />Return(@StrRet)<br />End<br /><br /></pre><br />-- Select dbo.No2Word('123456789')<br />-- Can Contact me @ mayursid@yahoo.co.in<br />
  2. fischerj New Member

    What language is this?

    It's not a stored procedure but looks somewhat like one.
  3. FrankKalis Moderator

    That is definitely T-SQL. You might note the CREATE FUNCTION at the beginning. [<img src='/community/emoticons/emotion-1.gif' alt=':)' />]<br />However I don't think this function does what it is supposed to do. Here's what someone added to a basic script of mine on SQL Server Central<br /><pre><br /> CREATE FUNCTION fn$_to_Dollars<br />(@Cash float)<br /> <br />RETURNS varchar(100)<br />AS<br />BEGIN<br />-- a5xo3z1 / Frank <br />-- SQL Server Guru - SQLServercentral.com<br />-- Germany <br />-- Posted - 08/14/2003 : 12:17:03 AM <br />-- Modified By Bill Nye 20030904 to work w/decimals and include hyphens <br />DECLARE @cents as tinyint, @fill as tinyint<br /><br />set @Cents=right(cast(round(@Cash*100,0) as bigint),2)<br /><br />BEGIN<br />DECLARE @i int<br />DECLARE @tmp char(1)<br />DECLARE @lpsz VARCHAR(20)<br />DECLARE @RetVal VARCHAR(255)<br />DECLARE @hyphen char(1), @flag char(1) <br /><br /><br />SELECT @lpsz=CONVERT(varchar(20), cast(@Cash as bigint)) --cast added, otherwise &gt;1mm converts to exponent and won't work<br />SELECT @i=LEN(@lpsz)<br />SELECT @RetVal=''<br />WHILE (@i&gt;0)<br />BEGIN<br /><br />SELECT @tmp=(SUBSTRING(@lpsz,@i,1))<br /><br />IF ((LEN(@lpsz)-@i) % 3)=1<br /><br />IF @tmp='1'<br />SELECT @RetVal=CASE (SUBSTRING(@lpsz,@i+1,1))<br />WHEN '0' THEN 'Ten'<br />WHEN '1' THEN 'Eleven'<br />WHEN '2' THEN 'Twelve'<br />WHEN '3' THEN 'Thirteen'<br />WHEN '4' THEN 'Fourteen'<br />WHEN '5' THEN 'Fifteen'<br />WHEN '6' THEN 'Sixteen'<br />WHEN '7' THEN 'Seventeen'<br />WHEN '8' THEN 'Eighteen'<br />WHEN '9' THEN 'Nineteen'<br />END+' '+CASE<br />WHEN ((LEN(@lpsz)-@i)=4) THEN 'Thousand '<br />WHEN ((LEN(@lpsz)-@i)=7) THEN 'Million '<br />WHEN ((LEN(@lpsz)-@i)=10) THEN 'Billion '<br />WHEN ((LEN(@lpsz)-@i)=13) THEN 'Trillion '<br />ELSE ''<br />END+@RetVal<br />ELSE<br />BEGIN<br />SELECT @RetVal=CASE (SUBSTRING(@lpsz,@i+1,1))<br />WHEN '0' THEN ''<br />WHEN '1' THEN 'One'<br />WHEN '2' THEN 'Two'<br />WHEN '3' THEN 'Three'<br />WHEN '4' THEN 'Four'<br />WHEN '5' THEN 'Five'<br />WHEN '6' THEN 'Six'<br />WHEN '7' THEN 'Seven'<br />WHEN '8' THEN 'Eight'<br />WHEN '9' THEN 'Nine'<br />END+' '+ CASE<br />WHEN ((LEN(@lpsz)-@i)=4) THEN 'Thousand '<br />WHEN ((LEN(@lpsz)-@i)=7) THEN 'Million '<br />WHEN ((LEN(@lpsz)-@i)=10) THEN 'Billion '<br />WHEN ((LEN(@lpsz)-@i)=13) THEN 'Trillion '<br />ELSE ''<br />END+@RetVal<br /><br />--*************** Added to include hyphens ie 53=fifty-three<br />begin<br />SELECT @hyphen=case(SUBSTRING(@lpsz,@i+1,1))<br />WHEN '0' THEN ' '<br />ELSE '-'<br />END<br />end<br />--***************<br />SELECT @RetVal=CASE @tmp<br />WHEN '0' THEN ''<br />WHEN '1' THEN 'Ten'<br />WHEN '2' THEN 'Twenty'<br />WHEN '3' THEN 'Thirty'<br />WHEN '4' THEN 'Forty'<br />WHEN '5' THEN 'Fifty'<br />WHEN '6' THEN 'Sixty'<br />WHEN '7' THEN 'Seventy'<br />WHEN '8' THEN 'Eighty'<br />WHEN '9' THEN 'Ninety'<br />END+@hyphen+@RetVal<br />END<br /><br />IF (((LEN(@lpsz)-@i) % 3)=2) OR (((LEN(@lpsz)-@i) % 3)=0) AND (@i=1) <br />BEGIN SELECT @RetVal=CASE @tmp<br />WHEN '0' THEN ''<br />WHEN '1' THEN 'One'<br />WHEN '2' THEN 'Two'<br />WHEN '3' THEN 'Three'<br />WHEN '4' THEN 'Four'<br />WHEN '5' THEN 'Five'<br />WHEN '6' THEN 'Six'<br />WHEN '7' THEN 'Seven'<br />WHEN '8' THEN 'Eight'<br />WHEN '9' THEN 'Nine'<br />END +' '+CASE<br />WHEN (@lpsz='0') THEN 'Zero'<br />WHEN (@tmp&lt;&gt;'0')AND( ((LEN(@lpsz)-@i) % 3)=2) THEN 'Hundred '<br />ELSE ''<br />END + CASE<br />WHEN ((LEN(@lpsz)-@i)=3) THEN 'Thousand '<br />WHEN ((LEN(@lpsz)-@i)=6) THEN 'Million '<br />WHEN ((LEN(@lpsz)-@i)=9) THEN 'Billion '<br />WHEN ((LEN(@lpsz)-@i)=12) THEN 'Trillion '<br />ELSE ''<br />END+ @RetVal<br />END<br />SELECT @i=@i-1<br /><br />END<br />-- Added 2 extra replaces to get rid of occasional extra space and hyphen when like 109, otherwise it shows "one hundred -nine" <br />SET @RetVal=REPLACE(REPLACE(REPLACE(@RetVal,' ',' '),' ',' '),' -',' ')<br /><br />SET @RetVal=@RetVal+'and ' +cast(@cents as char(2)) +'/100 '<br /><br />SELECT @fill= case sign(84-len(@RetVal)) -- Assumes length of 85 for amount field on check <br />WHEN -1.0 Then 0 -- otherwise, nothing shows<br /> Else 84-(len(@RetVal))<br />END<br /><br />RETURN @RetVal+Replicate('*',@fill)<br />END<br /><br />END<br />GO<br />Select dbo.fn$_to_Dollars('123456789')<br />drop function dbo.fn$_to_Dollars<br />---------------------------------------------------------------------------------------------------- <br />One Hundred Twenty-Three MillionFour Hundred Fifty-Six Thousand Seven Hundred Eighty-Nine and 0 /100<br /><br />(1 row(s) affected)<br /></pre><br />You can find it at<a target="_blank" href=http://www.sqlservercentral.com/forums/shwmessage.aspx?forumid=23&messageid=15272>http://www.sqlservercentral.com/forums/shwmessage.aspx?forumid=23&messageid=15272</a><br /><br /><br /><br /><br />-----------------------<br />--Frank<br /<a target="_blank" href=http://www.insidesql.de>http://www.insidesql.de</a><br />-----------------------<br />
  4. patel_mayur New Member

    <blockquote id="quote"><font size="1" face="Verdana, Arial, Helvetica" id="quote">quote:<hr height="1" noshade id="quote"><i>Originally posted by FrankKalis</i><br /><br />That is definitely T-SQL. You might note the CREATE FUNCTION at the beginning. [<img src='/community/emoticons/emotion-1.gif' alt=':)' />]<br />However I don't think this function does what it is supposed to do. Here's what someone added to a basic script of mine on SQL Server Central<br /><pre><br /> CREATE FUNCTION fn$_to_Dollars<br />(@Cash float)<br /> <br />RETURNS varchar(100)<br />AS<br />BEGIN<br />-- a5xo3z1 / Frank <br />-- SQL Server Guru - SQLServercentral.com<br />-- Germany <br />-- Posted - 08/14/2003 : 12:17:03 AM <br />-- Modified By Bill Nye 20030904 to work w/decimals and include hyphens <br />DECLARE @cents as tinyint, @fill as tinyint<br /><br />set @Cents=right(cast(round(@Cash*100,0) as bigint),2)<br /><br />BEGIN<br />DECLARE @i int<br />DECLARE @tmp char(1)<br />DECLARE @lpsz VARCHAR(20)<br />DECLARE @RetVal VARCHAR(255)<br />DECLARE @hyphen char(1), @flag char(1) <br /><br /><br />SELECT @lpsz=CONVERT(varchar(20), cast(@Cash as bigint)) --cast added, otherwise &gt;1mm converts to exponent and won't work<br />SELECT @i=LEN(@lpsz)<br />SELECT @RetVal=''<br />WHILE (@i&gt;0)<br />BEGIN<br /><br />SELECT @tmp=(SUBSTRING(@lpsz,@i,1))<br /><br />IF ((LEN(@lpsz)-@i) % 3)=1<br /><br />IF @tmp='1'<br />SELECT @RetVal=CASE (SUBSTRING(@lpsz,@i+1,1))<br />WHEN '0' THEN 'Ten'<br />WHEN '1' THEN 'Eleven'<br />WHEN '2' THEN 'Twelve'<br />WHEN '3' THEN 'Thirteen'<br />WHEN '4' THEN 'Fourteen'<br />WHEN '5' THEN 'Fifteen'<br />WHEN '6' THEN 'Sixteen'<br />WHEN '7' THEN 'Seventeen'<br />WHEN '8' THEN 'Eighteen'<br />WHEN '9' THEN 'Nineteen'<br />END+' '+CASE<br />WHEN ((LEN(@lpsz)-@i)=4) THEN 'Thousand '<br />WHEN ((LEN(@lpsz)-@i)=7) THEN 'Million '<br />WHEN ((LEN(@lpsz)-@i)=10) THEN 'Billion '<br />WHEN ((LEN(@lpsz)-@i)=13) THEN 'Trillion '<br />ELSE ''<br />END+@RetVal<br />ELSE<br />BEGIN<br />SELECT @RetVal=CASE (SUBSTRING(@lpsz,@i+1,1))<br />WHEN '0' THEN ''<br />WHEN '1' THEN 'One'<br />WHEN '2' THEN 'Two'<br />WHEN '3' THEN 'Three'<br />WHEN '4' THEN 'Four'<br />WHEN '5' THEN 'Five'<br />WHEN '6' THEN 'Six'<br />WHEN '7' THEN 'Seven'<br />WHEN '8' THEN 'Eight'<br />WHEN '9' THEN 'Nine'<br />END+' '+ CASE<br />WHEN ((LEN(@lpsz)-@i)=4) THEN 'Thousand '<br />WHEN ((LEN(@lpsz)-@i)=7) THEN 'Million '<br />WHEN ((LEN(@lpsz)-@i)=10) THEN 'Billion '<br />WHEN ((LEN(@lpsz)-@i)=13) THEN 'Trillion '<br />ELSE ''<br />END+@RetVal<br /><br />--*************** Added to include hyphens ie 53=fifty-three<br />begin<br />SELECT @hyphen=case(SUBSTRING(@lpsz,@i+1,1))<br />WHEN '0' THEN ' '<br />ELSE '-'<br />END<br />end<br />--***************<br />SELECT @RetVal=CASE @tmp<br />WHEN '0' THEN ''<br />WHEN '1' THEN 'Ten'<br />WHEN '2' THEN 'Twenty'<br />WHEN '3' THEN 'Thirty'<br />WHEN '4' THEN 'Forty'<br />WHEN '5' THEN 'Fifty'<br />WHEN '6' THEN 'Sixty'<br />WHEN '7' THEN 'Seventy'<br />WHEN '8' THEN 'Eighty'<br />WHEN '9' THEN 'Ninety'<br />END+@hyphen+@RetVal<br />END<br /><br />IF (((LEN(@lpsz)-@i) % 3)=2) OR (((LEN(@lpsz)-@i) % 3)=0) AND (@i=1) <br />BEGIN SELECT @RetVal=CASE @tmp<br />WHEN '0' THEN ''<br />WHEN '1' THEN 'One'<br />WHEN '2' THEN 'Two'<br />WHEN '3' THEN 'Three'<br />WHEN '4' THEN 'Four'<br />WHEN '5' THEN 'Five'<br />WHEN '6' THEN 'Six'<br />WHEN '7' THEN 'Seven'<br />WHEN '8' THEN 'Eight'<br />WHEN '9' THEN 'Nine'<br />END +' '+CASE<br />WHEN (@lpsz='0') THEN 'Zero'<br />WHEN (@tmp&lt;&gt;'0')AND( ((LEN(@lpsz)-@i) % 3)=2) THEN 'Hundred '<br />ELSE ''<br />END + CASE<br />WHEN ((LEN(@lpsz)-@i)=3) THEN 'Thousand '<br />WHEN ((LEN(@lpsz)-@i)=6) THEN 'Million '<br />WHEN ((LEN(@lpsz)-@i)=9) THEN 'Billion '<br />WHEN ((LEN(@lpsz)-@i)=12) THEN 'Trillion '<br />ELSE ''<br />END+ @RetVal<br />END<br />SELECT @i=@i-1<br /><br />END<br />-- Added 2 extra replaces to get rid of occasional extra space and hyphen when like 109, otherwise it shows "one hundred -nine" <br />SET @RetVal=REPLACE(REPLACE(REPLACE(@RetVal,' ',' '),' ',' '),' -',' ')<br /><br />SET @RetVal=@RetVal+'and ' +cast(@cents as char(2)) +'/100 '<br /><br />SELECT @fill= case sign(84-len(@RetVal)) -- Assumes length of 85 for amount field on check <br />WHEN -1.0 Then 0 -- otherwise, nothing shows<br /> Else 84-(len(@RetVal))<br />END<br /><br />RETURN @RetVal+Replicate('*',@fill)<br />END<br /><br />END<br />GO<br />Select dbo.fn$_to_Dollars('123456789')<br />drop function dbo.fn$_to_Dollars<br />---------------------------------------------------------------------------------------------------- <br />One Hundred Twenty-Three MillionFour Hundred Fifty-Six Thousand Seven Hundred Eighty-Nine and 0 /100<br /><br />(1 row(s) affected)<br /></pre><br />You can find it at<a target="_blank" href=http://www.sqlservercentral.com/forums/shwmessage.aspx?forumid=23&messageid=15272>http://www.sqlservercentral.com/forums/shwmessage.aspx?forumid=23&messageid=15272</a><br /><br /><br /><br /><br />-----------------------<br />--Frank<br /<a target="_blank" href=http://www.insidesql.de>http://www.insidesql.de</a><br />-----------------------<br /><br /><hr height="1" noshade id="quote"></font id="quote"></blockquote id="quote">
  5. FrankKalis Moderator

  6. patel_mayur New Member

    The function is written as per Indian Currency and will work fine for it.

    I m very thankful to Frank as he has modified it and enabled it to use by
    everyone.

    Thanks to Frank.
  7. FrankKalis Moderator

    Sorry, I wasn't aware of the Indian monetary units, so "Lack" or "Crore" sounded strange to me [<img src='/community/emoticons/emotion-1.gif' alt=':)' />]<br /><br />-----------------------<br />--Frank<br /<a target="_blank" href=http://www.insidesql.de>http://www.insidesql.de</a><br />-----------------------<br />
  8. Madhivanan Moderator


    Both of yours are very useful

    Madhivanan

Share This Page