mysql 阿拉伯數字轉換中文函數

CREATE DEFINER=`root`@`%` FUNCTION `sp_cn`(n_LowerMoney  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(n_LowerMoney,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,'零零','零') ;

 

if ( '元' = substring(v_UpperStr,1,1))  then

set v_UpperStr = substring(v_UpperStr,2,(char_length(v_UpperStr) - 1));

end if;

if ( '零' = substring(v_UpperStr,1,1)) then

set v_UpperStr = substring(v_UpperStr,2,(char_length(v_UpperStr) - 1)) ;

end if;

if ( '角' = substring(v_UpperStr,1,1)) then

set v_UpperStr = substring(v_UpperStr,2,(char_length(v_UpperStr) - 1)) ;

end if;

if ( '分' = substring(v_UpperStr,1,1)) then

set v_UpperStr = substring(v_UpperStr,2,(char_length(v_UpperStr) - 1)) ;

end if;

if ('整' = substring(v_UpperStr,1,1)) then

set v_UpperStr = '零元整' ;

end if;

return  v_UpperStr;

END;