CREATE DEFINER="root"@"%" FUNCTION "number_tochar"(nums varchar(20)) RETURNS varchar(11) CHARSET utf8
BEGIN
#Routine body goes here...
DECLARE res VARCHAR(20);
DECLARE strs VARCHAR(20);
DECLARE i int;
set res ='';
set strs ='';
set i = length(nums);
WHILE i > 0 DO
set res = SUBSTRING(nums,length(nums)-i+1,1);
set res = CASE res
WHEN 1 THEN '一'
WHEN 2 THEN '二'
WHEN 3 THEN '三'
WHEN 4 THEN '四'
WHEN 5 THEN '五'
WHEN 6 THEN '六'
WHEN 7 THEN '七'
WHEN 8 THEN '八'
WHEN 9 THEN '九'
ELSE '零'
END;
set res = CONCAT(res,CASE i
WHEN 2 THEN '十'
WHEN 3 THEN '百'
WHEN 4 THEN '千'
WHEN 5 THEN '万'
WHEN 6 THEN '十万'
WHEN 9 THEN '亿'
ELSE ''
END );
SET strs = CONCAT(strs,res);
#SET strs =replace(strs,'零亿','零');
#SET strs =replace(strs,'零万','零');
#SET strs =replace(strs,'零千','零');
#SET strs =replace(strs,'零百','零');
#SET strs =replace(strs,'零十','零');
#SET strs =replace(strs,'零零','零');
SET i = i -1;
END WHILE;
RETURN strs;
END
测试:
结果: