一个用来将数字转换为英文的MySql函数

网上很容易找到SQL Server等其它数据库转英文数字的函数，但是MySql我没有找到，故写了下来：

DELIMITER $$

CREATE FUNCTION ConvertThreeDigitInteger2EnWords(numStr char(3)) RETURNS varchar(50)
    DETERMINISTIC
BEGIN
	/*
    此函数接受一个形容'010'的用3位字符表示的数字，返回这个数字的英文表达。如果传入'000'，返回空字符
    numStr：用字符表示的3位数字，左边不足需补0
    */
    declare numEnDisplay varchar(50) default ''; --  英文显示
    
    declare hundredEn varchar(50) default ''; -- 百位数
    declare tenEn varchar(50) default '';  -- 十位数
    declare numEn varchar(50) default ''; -- 个位数
    
    declare hundredNum char(1);  -- 百位数
    declare tenNum char(1);  -- 十位数
    declare num char(1); -- 个位数
  
    set hundredNum = substr(numStr, 1, 1);  
    set tenNum = substr(numStr, 2, 1); 
    set num = substr(numStr, 3, 1); 
    
    case hundredNum
        when '1' then set hundredEn = 'one hundred';
        when '2' then set hundredEn = 'two hundred';
        when '3' then set hundredEn = 'three hundred';
        when '4' then set hundredEn = 'four hundred';
        when '5' then set hundredEn = 'five hundred';
        when '6' then set hundredEn = 'six hundred';
        when '7' then set hundredEn = 'seven hundred';
        when '8' then set hundredEn = 'eight hundred';
        when '9' then set hundredEn = 'nine hundred';
        else set hundredEn = '';
    end case;
  
    if tenNum = '1' 
    then 
        case num
            when '1' then set tenEn = 'eleven';
            when '2' then set tenEn = 'twelve';
            when '3' then set tenEn = 'thirteen';
            when '4' then set tenEn = 'fourteen';
            when '5' then set tenEn = 'fifteen';
            when '6' then set tenEn = 'sixteen';
            when '7' then set tenEn = 'seventeen';
            when '8' then set tenEn = 'eighteen';
            when '9' then set tenEn = 'nineteen';
            else set tenEn = 'ten';
        end case;
    else 
        case tenNum
            when '2' then set tenEn = 'twenty';
            when '3' then set tenEn = 'thirty';
            when '4' then set tenEn = 'forty';
            when '5' then set tenEn = 'fifty';
            when '6' then set tenEn = 'sixty';
            when '7' then set tenEn = 'seventy';
            when '8' then set tenEn = 'eighty';
            when '9' then set tenEn = 'ninety';
            else set tenEn = '';
        end case;        
        case num
            when '1' then set numEn = 'one';
            when '2' then set numEn = 'two';
            when '3' then set numEn = 'three';
            when '4' then set numEn = 'four';
            when '5' then set numEn = 'five';
            when '6' then set numEn = 'six';
            when '7' then set numEn = 'seven';
            when '8' then set numEn = 'eight';
            when '9' then set numEn = 'nine';
            else set numEn = '';
        end case;
    end if;
    
    if hundredEn != '' then
        set numEnDisplay = hundredEn;
    end if;
    
    if hundredEn != '' and (tenEn != '' or numEn != '') then
        set numEnDisplay = CONCAT(numEnDisplay, ' and ');
    end if;
    
    if tenEn != '' then
        set numEnDisplay = CONCAT(numEnDisplay, tenEn);
    end if;
    
    if numEn != '' then
        if tenEn != '' then
            set numEnDisplay = CONCAT(numEnDisplay, '-', numEn);
        else
            set numEnDisplay = CONCAT(numEnDisplay, numEn);
        end if;
    end if;
    
    RETURN numEnDisplay;
END$$

DELIMITER ;

DELIMITER $$

CREATE FUNCTION ConvertNumber2EnWords(num decimal(14,2)) RETURNS varchar(200)
    DETERMINISTIC
BEGIN

   /*
   将一个数字转换为英文，最多能处理整数部分12位，小数部分为2位的数字。
    
   本函数遵循的规则：
   
   1，如果是0.00，返回 zero
   2，小数末尾0会被省略
   3，小数部分是00，末尾加 only
   4, 十位数前面会加 and，如 two hundred and forty
   */
   
    declare display varchar(200) default '';
    
    declare numStr varchar(15); -- 将数字转成字符串存于此
    declare pointIndex int; -- 小数点的位置
    declare numStrBeforePoint varchar(12); -- 整数部分
    declare numStrAfterPoint varchar(2); -- 小数部分
  
    declare billion char(3) default ''; -- billion 3 位
    declare million char(3) default ''; -- million 3 位
    declare thousand char(3) default ''; -- thousand 3 位
    declare low char(3) default ''; -- 最低 3 位
    
    declare billionEn varchar(50) default ''; -- billion 英文
    declare millionEn varchar(50) default ''; -- million 英文
    declare thousandEn varchar(50) default ''; -- thousand 英文
    declare lowEn varchar(50) default ''; -- 最低 3 位 英文    
    
    declare numCharAfterPointIndex int; -- 正在处理的小数位
    declare numCharAfterPoint char(1); -- 正在处理的小数

    if num = 0 then
        return 'zero'; -- 如果是0，提前返回
    end if;
    
    set numStr = cast(num as char);    
  
    set pointIndex = locate('.', numStr);
    
    if pointIndex = 0 then
        set numStrBeforePoint = numStr;
    else
        set numStrBeforePoint = substr(numStr, 1, pointIndex - 1);
    end if;
    
    set numStrBeforePoint = lpad(numStrBeforePoint, 12, '0'); -- 左边补0
    set numStrAfterPoint = trim(TRAILING '0' from substr(numStr, pointIndex + 1, 2));
    
    set billion = substr(numStrBeforePoint, 1, 3);
    set million = substr(numStrBeforePoint, 4, 3);
    set thousand = substr(numStrBeforePoint, 7, 3);
    set low = substr(numStrBeforePoint, 10, 3);
    
    set billionEn = ConvertThreeDigitInteger2EnWords(billion);
    set millionEn = ConvertThreeDigitInteger2EnWords(million);
    set thousandEn = ConvertThreeDigitInteger2EnWords(thousand);
    set lowEn = ConvertThreeDigitInteger2EnWords(low);
    
    if billionEn != '' then
        set display = concat(display, billionEn, ' billion');
    end if;
    
    if millionEn != '' then
        set display = concat(display, ' ', millionEn, ' million');
    end if;
    
    if thousandEn != '' then
        set display = concat(display, ' ', thousandEn, ' thousand');
    end if;    
   
    if low != '000' then
        if display = '' then
            set display = lowEn;
        else
            if substr(low, 1, 1) != '0' then
                set display = concat(display, ' ', lowEn);
            else
                set display = concat(display, ' and ', lowEn);
            end if;
        end if;
    end if;
    
    if numStrAfterPoint = '' then
        set display = concat(display, ' only');
    else
         -- 处理小数部分
         
        if display = '' then
            set display = 'zero'; -- 0.xx
        end if;
        
        set display = concat(display, ' point');
        set numCharAfterPointIndex = 1;
        while numCharAfterPointIndex <= length(numStrAfterPoint) do
            set numCharAfterPoint = substr(numStrAfterPoint, numCharAfterPointIndex, 1);
            case numCharAfterPoint
                when '1' then set display = concat(display, ' one');
                when '2' then set display = concat(display, ' two');
                when '3' then set display = concat(display, ' three');
                when '4' then set display = concat(display, ' four');
                when '5' then set display = concat(display, ' five');
                when '6' then set display = concat(display, ' six');
                when '7' then set display = concat(display, ' seven');
                when '8' then set display = concat(display, ' eight');
                when '9' then set display = concat(display, ' nine');
                else set display = concat(display, ' zero');
            end case;
            set numCharAfterPointIndex = numCharAfterPointIndex + 1;
        end while;
    end if;
   
RETURN display;
END$$

DELIMITER ;