Converting Numbers to Words in MYSQL result! Using Query

I am trying to print a output as follows when I execute a query in mysql.

Name    Salary  Sal_in_Words
Mohan   45000   Rupees Forty Five Thousand Only

The column Salary has a value 45000 and in the third column the Value in second colunm is converted to words through Query.

I found some article where in Oracle we can get the result as above using the below query:

select Salary, (' Rs. '|| (to_char(to_date(sal,'j'), 'Jsp'))|| ' only.')) Sal_in_Words from employee

OUTPUT:

Name    Salary  Sal_in_Words
Suresh  45000   Rupees Forty Five Thousand Only

In MySQL I tried LPAD,RPAD but those just adds strings to the result not converting words to string.

I found some tutorial but all those explained about “TO_CHAR (dates)”.

Is there any way to do it?

Answer

PROCEDURE TO GENERATE NUMBER TO WORDS FORMAT...........

CREATE DEFINER=`root`@`localhost` PROCEDURE `xnum2txt`(in nNum int(8))
BEGIN
   declare cRetVal,cTxt,cX1,cX2 varchar(500);
    set nNum=floor(nNum);
    set cRetval='';
    set cTxt='';
            if nnum<0 then
                set cRetval="-"+ xNum22Txt(nnum*-1);
            elseif nNum<11 then 
                set cTxt="Zero One  Two  ThreeFour Five Six  SevenEightNine Ten  ";
                set cRetVal= trim(substring(cTxt,(nNum * 5) + 1,5)) ;
            ELSEIF nNum < 20 then 
                set cTxt = "Eleven   Twelve   Thirteen Fourteen Fifteen  Sixteen  SeventeenEighteen Nineteen ";
                set cRetval = trim(substring(cTxt,((nNum - 11) * 9) + 1,9));
            ELSEIF nNum < 30 then
                  set cRetval = "Twenty" + if(nNum > 20,"-" + xnum22txt(nNum-20)," ");
            ELSEIF nNum < 40 then
                  set cRetval = "Thirty" + if(nNum > 30,"-" + xnum22txt(nNum-30)," ");
            ELSEIF nNum < 50 then 
                  set cRetval = "Forty" + if(nNum > 40,"-" + xnum22txt(nNum-40)," ");
            ELSEIF nNum < 60 then
                  set cRetval = "Fifty" + if(nNum > 50,"-" + xnum22txt(nNum-50)," ");
            ELSEIF nNum < 70 then
                  set cRetval = "Sixty" + if(nNum > 60,"-" + xnum22txt(nNum-60)," ");
            ELSEIF nNum < 80 then
                  set cRetval = "Seventy" + if(nNum > 70,"-" + xnum22txt(nNum-70)," ");
            ELSEIF nNum < 90 then
                  set cRetval = "Eighty" + if(nNum > 80,"-" + xnum22txt(nNum-80)," ");
            ELSEIF nNum < 100 then
                  set cRetval = "Ninety" + if(nNum > 90,"-" + xnum22txt(nNum-90)," ");
            ELSEIF nNum < 1000 then
                  set cX1 = floor(nNum/100);
                  set cX2 = nNum-(cX1*100);
                  set cRetval = xnum22txt(cX1) + "Hundred" + if(cX2 > 0," " + xnum22txt(cX2)," ");
           ELSEIF nNum < 1000000 then
                  set cX1 = floor(nNum/1000);
                  set cX2 = nNum-(cX1*1000);
                  set cRetval = xnum22txt(cX1) + "Thousand" + if(cX2 > 0,", " + xnum22txt(cX2)," ");
           ELSEIF nNum < 1000000000 then
                  set cX1 = floor(nNum/1000000);
                  set cX2 = nNum-(cX1*1000000);
                  set cRetval = xnum22txt(cX1) + "Million" + if(cX2 > 0,", " + xnum22txt(cX2)," ");
           ELSEIF nNum < 1000000000000 then
                  set cX1 = floor(nNum/1000000000);
                  set cX2 = nNum-(cX1*1000000000);
                  set cRetval = xnum22txt(cX1) + "Billion" + if(cX2 > 0,", " + xnum22txt(cX2)," ");
            ELSE
                  set cRetval = "***";
            end if;
            select cRetVal;
END