MySQL function for amount to words

The following MySQL functions used to convert number to words. If we need to print amount in words this function will help.

You need to create 2 functions to achieve this.
1) AmountToWords
2) Number2Char

Usage : SELECT AmountToWords(4896.50)
OutPut : ( Rupees Four Thousand Eight Hundred Ninety Six and Paise Fifty Only. )

AmountToWords Function code

CREATE DEFINER=`pmc_dms_user`@`%` FUNCTION `AmountToWords`(
    `nAmount` DECIMAL(11,2))
RETURNS varchar(2000) CHARSET utf8
LANGUAGE SQL
DETERMINISTIC
CONTAINS SQL
SQL SECURITY DEFINER
COMMENT ''
BEGIN

DECLARE AMT_Words   VARCHAR(2000) ;
DECLARE AMT_Text1   VARCHAR(2000) ;
DECLARE AMT_Text2   VARCHAR(2000) ;
DECLARE AMT_Text3   VARCHAR(2000) ;
DECLARE Words          VARCHAR(2000) ;
DECLARE Rupee_Txt      VARCHAR(2000) ;
DECLARE RupeeTxt      VARCHAR(2000) ;
DECLARE SP_Txt1      VARCHAR(2000) ;
DECLARE SP_Txt2      VARCHAR(2000) ;
DECLARE SP_Txt3      VARCHAR(2000) ;
DECLARE Decimal_Txt VARCHAR(20)      ;

DECLARE WP              INT;
DECLARE SP              INT;
DECLARE RLEN          INT;
DECLARE PLEN        INT;
DECLARE _Rupee          INT;
DECLARE _Decimal      INT;
  
  
    SET AMT_Words   = '( Rupees ';
    SET AMT_Text1   = ' One ,Two ,Three ,Four ,Five ,Six ,Seven ,Eight ,Nine ,Ten ';
    SET AMT_Text1   = CONCAT(AMT_Text1,',Eleven ,Twelve ,Thirteen ,Forteen ,Fifteen ,Sixteen ,Seventeen ,Eighteen ,Nineteen ,Twenty ');
    SET AMT_Text2   = ' Twenty ,Thirty ,Fourty ,Fifty ,Sixty ,Seventy ,Eighty ,Ninety ';
    SET AMT_Text3   = ' Crore ,Lakh ,Thousand ,Hundred , ';  
    SET Rupee_Txt   = '';
    SET RupeeTxt    = '';
    SET Decimal_Txt = '';
    SET SP_Txt3     = '';
    SET WP              = 1 ;
    SET SP             = 1 ;
    SET Words       = '';
    SET _Rupee      = TRUNCATE(nAmount,0);
    SET RLEN             = 9-LENGTH(_Rupee);
    SET Rupee_Txt   = CONCAT(SPACE(RLEN),CONVERT(_Rupee,CHAR)) ;
  
    SET _Decimal    = CAST(SUBSTRING_INDEX(nAmount,'.',-1) AS UNSIGNED);  
    SET PLEN          = 2-LENGTH(_Decimal);
    SET Decimal_Txt = CONCAT(SPACE(PLEN),CONVERT(_Decimal,CHAR));


    WHILE WP < 6 DO
  
       IF SP =  7 THEN
         
           SET RupeeTxt = SUBSTRING(Rupee_Txt,SP,1);
               SET SP =SP+1;
           SET RupeeTxt = CONCAT('0',RupeeTxt);
           SET Words=Number2Char(RupeeTxt,AMT_Text1,AMT_Text2);
                 
       ELSE
                          
          SET RupeeTxt = SubString(Rupee_Txt,SP,2);
          SET SP =SP+2;
           SET Words=Number2Char(RupeeTxt,AMT_Text1,AMT_Text2);
                                   
       END IF; 
     
       IF Words != '' THEN
    
           IF AMT_Words != '' AND WP = 6 THEN         
               SET AMT_Words =CONCAT(AMT_Words,'and ');      
           END IF;
          
            SET SP_Txt1 = SUBSTRING_INDEX(AMT_Text3,',',WP);
            SET SP_Txt2 = SUBSTRING_INDEX(SP_Txt1,',',WP-1);
            SET SP_Txt3 = SUBSTRING(SP_Txt1,LENGTH(SP_Txt2)+2,LENGTH(SP_Txt1));
           SET AMT_Words = CONCAT(AMT_Words,Words,SP_Txt3) ;
         
       END IF;
     
       SET WP = WP+1; 
          
    END WHILE;

    IF AMT_Words != '' THEN
        SET AMT_Words = CONCAT(AMT_Words,' ');
    END IF;
  
    /*End of Rupee Side*/
  
    SET Words=Number2Char(Decimal_Txt,AMT_Text1,AMT_Text2);
  
    IF Words != '' AND AMT_Words != "" THEN
        SET AMT_Words = CONCAT(TRIM(AMT_Words),' and ', 'Paise ');
    END IF;
  
    IF Words != '' THEN
            SET AMT_Words = CONCAT(AMT_Words,Words);
    END IF;
  
    IF AMT_Words != '' THEN
        SET AMT_Words = CONCAT(TRIM(AMT_Words),' Only. )');
    END IF;

    RETURN AMT_Words;
 
END
Number2Char Function Code

CREATE DEFINER=`pmc_dms_user`@`%` FUNCTION `Number2Char`(
    `cDigits` VARCHAR(500),
    `cAMT_Text1` VARCHAR(500),
    `cAMT_Text2` VARCHAR(500))
RETURNS varchar(2000) CHARSET utf8
LANGUAGE SQL
DETERMINISTIC
CONTAINS SQL
SQL SECURITY DEFINER
COMMENT ''
BEGIN

DECLARE Words VARCHAR(2000);
DECLARE AMT_Txt1 VARCHAR(2000);
DECLARE AMT_Txt2 VARCHAR(2000);
DECLARE AMT_Txt3 VARCHAR(2000);
DECLARE Digits INT;
DECLARE Value  INT;

SET Words ='';
    IF cDigits > '20' THEN  
 
        SET Digits = CAST(LEFT(cDigits,1) AS UNSIGNED)-1;
      
        SET AMT_Txt1 = SUBSTRING_INDEX(cAMT_Text2,',',Digits);
        SET AMT_Txt2 = SUBSTRING_INDEX(AMT_Txt1,',',Digits-1);  
        SET Words =SUBSTRING(AMT_Txt1,LENGTH(AMT_Txt2)+2,LENGTH(AMT_Txt1));
      
        SET cDigits = CONCAT("0",RIGHT(cDigits,1)) ;
      
    END IF;
  
        SET Digits = CAST(cDigits AS UNSIGNED);
  
        SET AMT_Txt1 = SUBSTRING_INDEX(cAMT_Text1,',',Digits);
        SET AMT_Txt2 = SUBSTRING_INDEX(AMT_Txt1,',',Digits-1);  
        SET AMT_Txt3 = SUBSTRING(AMT_Txt1,LENGTH(AMT_Txt2)+2,LENGTH(AMT_Txt1));
        SET Words =CONCAT(Words,AMT_Txt3);

RETURN Words;

END