网上很容易找到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 ;
