72 lines
2.1 KiB
SQL
72 lines
2.1 KiB
SQL
DROP FUNCTION ArabicToChinese;
|
|
DELIMITER $$
|
|
|
|
CREATE FUNCTION `ArabicToChinese`(ArabicNumerals DECIMAL(15,2)) RETURNS VARCHAR(200) CHARSET utf8
|
|
BEGIN
|
|
DECLARE v_LowerStr VARCHAR(200) ;
|
|
DECLARE v_UpperPart VARCHAR(200) ;
|
|
DECLARE v_UpperStr VARCHAR(200) ;
|
|
DECLARE i_I INT ;
|
|
SET v_LowerStr = LTRIM(RTRIM(ROUND(ArabicNumerals,2 ) ) ) ;
|
|
SET i_I = 1 ;
|
|
SET v_UpperStr = '' ;
|
|
WHILE ( i_I <=CHAR_LENGTH(v_LowerStr ) ) DO
|
|
SET v_UpperPart = CONCAT(
|
|
CASE SUBSTRING(v_LowerStr,CHAR_LENGTH(v_LowerStr) - i_I + 1,1 )
|
|
WHEN '.' THEN ''
|
|
WHEN '0' THEN '零'
|
|
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 '九'
|
|
END,
|
|
CASE i_I
|
|
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 '拾'
|
|
WHEN 10 THEN '佰'
|
|
WHEN 11 THEN '仟'
|
|
WHEN 12 THEN '亿'
|
|
WHEN 13 THEN '拾'
|
|
WHEN 14 THEN '佰'
|
|
WHEN 15 THEN '仟'
|
|
WHEN 16 THEN '万'
|
|
ELSE ''
|
|
END );
|
|
|
|
SET v_UpperStr =CONCAT( v_UpperPart , v_UpperStr) ;
|
|
SET i_I = i_I + 1 ;
|
|
END WHILE;
|
|
SET v_UpperStr = REPLACE(v_UpperStr,'零拾','零') ;
|
|
SET v_UpperStr = REPLACE(v_UpperStr,'零佰','零') ;
|
|
SET v_UpperStr = REPLACE(v_UpperStr,'零仟','零') ;
|
|
SET v_UpperStr = REPLACE(v_UpperStr,'零零零','零') ;
|
|
SET v_UpperStr = REPLACE(v_UpperStr,'零零','零') ;
|
|
SET v_UpperStr = REPLACE(v_UpperStr,'零角零分','零') ;
|
|
SET v_UpperStr = REPLACE(v_UpperStr,'零分','零') ;
|
|
SET v_UpperStr = REPLACE(v_UpperStr,'零角','零') ;
|
|
SET v_UpperStr = REPLACE(v_UpperStr,'零亿零万零','亿') ;
|
|
SET v_UpperStr = REPLACE(v_UpperStr,'亿零万零','亿') ;
|
|
SET v_UpperStr = REPLACE(v_UpperStr,'零亿零万','亿') ;
|
|
SET v_UpperStr = REPLACE(v_UpperStr,'零万零','万') ;
|
|
SET v_UpperStr = REPLACE(v_UpperStr,'万零','万') ;
|
|
SET v_UpperStr = REPLACE(v_UpperStr,'零亿','亿') ;
|
|
SET v_UpperStr = REPLACE(v_UpperStr,'零万','万') ;
|
|
SET v_UpperStr = REPLACE(v_UpperStr,'零','') ;
|
|
SET v_UpperStr = REPLACE(v_UpperStr,'零零','零') ;
|
|
SET v_UpperStr = REPLACE(v_UpperStr,'一十','十') ;
|
|
RETURN v_UpperStr;
|
|
|
|
END;
|