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
