实现阿拉伯数字转为中文的MYsql函数

希望猜中了你的需求。mysql> set @x='12345';
Query OK, 0 rows affected (0.00 sec)mysql> select @x,replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(@x,
    ->  '1','一'),
    ->  '2','二'),
    ->  '3','三'),
    ->  '4','四'),
    ->  '5','五'),
    ->  '6','六'),
    ->  '7','七'),
    ->  '8','八'),
    ->  '9','九'),
    ->  '0','零') as k;
+-------+------------+
| @x    | k          |
+-------+------------+
| 12345 | 一二三四五 |
+-------+------------+
1 row in set (0.00 sec)mysql>

(不要高估你的汉语表达能力或者我的汉语理解能力)
   建议你列出你的表结构，并提供测试数据以及基于这些测试数据的所对应正确结果。
   参考一下这个贴子的提问方式http://topic.csdn.net/u/20091130/20/8343ee6a-417c-4c2d-9415-fa46604a00cf.html

   1. 你的 create table xxx .. 语句
   2. 你的 insert into xxx ... 语句
   3. 结果是什么样，（并给以简单的算法描述）
   4. 你用的数据库名称和版本(经常有人在MS SQL server版问 MySQL)

   这样想帮你的人可以直接搭建和你相同的环境，并在给出方案前进行测试，避免文字描述理解上的误差。

呵呵
谢谢楼上提醒！我的需求是这样
用一个MYSQL存储过程实现如下功能
例如：调用存储过程sp_money（123.45）
得出
“壹百贰拾叁圆肆角伍分”
的结果；
求这样的存储过程！

这是我参照MSSQL写的，但是运行结果不对。请高手指点：
CREATE DEFINER=`root`@`%` FUNCTION `sp_cn1`(ChangeMoney varchar(200)) RETURNS varchar(200) CHARSET utf8
beginDeclare String1 char(20);Declare String2 char(30);Declare String4 Varchar(100);Declare String3 Varchar(100);Declare i int;Declare j int;Declare Ch1 Varchar(100);Declare Ch2 Varchar(100);Declare Zero bigint;Declare ReturnValue VarChar(100);set ReturnValue = '';set String1 = '零壹贰叁肆伍陆柒捌玖';set String2 = '万仟佰拾亿仟佰拾万仟佰拾元角分';set String4 =CAST(ChangeMoney*100 as char);set j=length(cast((ChangeMoney*100) as char ));set String2=Right(String2,J);set i = 1;while ( i<= j ) doset String3 = Substring(String4,i,1); if String3<>'0 ' THEN set Ch1 = Substring(String1, Cast(String3 as char ) + 1, 1); set Ch2 = Substring(String2, i, 1); set Zero = 0 ; else If (Zero = 0 ) Or (i = J - 9) Or (i = J - 5) Or (i = J - 1) then set Ch1 = '零'; Else set Ch1 = ''; set Zero = Zero + 1 ; END IF;If i = J - 10 THENset Ch2 = '亿';set Zero = 0;end IF;If i = J - 6 THENset Ch2 = '万';set Zero = 0;end IF;if i = J - 2 THENset Ch2 = '元';set Zero = 0;end IF;If i = J THENset Ch2 = '整';end IF ; end if;SET ReturnValue = ReturnValue + Ch1 + Ch2;SET i = i+1;end while;If LOCATE('仟仟',ReturnValue) <> 0 thenset ReturnValue = Replace(ReturnValue, '仟仟', '仟');end if;If LOCATE('佰佰',ReturnValue) <> 0 thenset ReturnValue = Replace(ReturnValue, '佰佰', '佰');end if;If LOCATE('零元',ReturnValue) <> 0 thenset ReturnValue = Replace(ReturnValue, '零元', '元');end if;If LOCATE('零万',ReturnValue) <> 0 thenset ReturnValue = Replace(ReturnValue, '零万', '万');end if;If LOCATE('零亿',ReturnValue) <> 0 thenset ReturnValue = Replace(ReturnValue, '零亿', '亿');end if;If LOCATE('零整',ReturnValue) <> 0 thenset ReturnValue = Replace(ReturnValue, '零整', '整');end if;If LOCATE('零佰',ReturnValue) <> 0 thenset ReturnValue = Replace(ReturnValue, '零佰', '零');end if;If LOCATE('零仟',ReturnValue) <> 0 thenset ReturnValue = Replace(ReturnValue, '零仟', '零');end if;If LOCATE('元元',ReturnValue) <> 0 thenset ReturnValue = Replace(ReturnValue, '元元', '元');end if;return ReturnValue;end;

我摸索了一下
修改后的存储过程，还是有点不正确。当入参是小数的时候返回的中文数字是错误，
请教高手指点
具体代码：
CREATE DEFINER=`root`@`%` FUNCTION `sp_cn1`(xx float) RETURNS varchar(200) CHARSET utf8
beginDeclare String1 char(20);Declare String2 char(30);Declare String4 Varchar(100);Declare String3 Varchar(100);Declare i int;Declare j int;Declare Ch1 Varchar(100);Declare Ch2 Varchar(100);Declare Zero bigint;Declare ReturnValue VarChar(100);set @ReturnValue = '';SET @String1 = '零壹贰叁肆伍陆柒捌玖';SET @String2 = '万仟佰拾亿仟佰拾万仟佰拾元角分';SET @String4 =CAST(XX*100 as char);SET j=length(cast((XX*100) as char ));set @String2=Right(@String2,J);set i = 1;while ( i<= j ) doset @String3 = Substring(@String4,i,1); if @String3<>'0 ' THEN set @Ch1 = Substring(@String1, Cast(@String3 as char ) + 1, 1); set @Ch2 = Substring(@String2, i, 1); set Zero = 0 ; else If (Zero = 0 ) Or (i = J - 9) Or (i = J - 5) Or (i = J - 1) then set @Ch1 = '零'; Else set @Ch1 = ''; end if; set Zero = Zero + 1 ; END IF;If i = J - 10 THENset @Ch2 = '亿';set Zero = 0;end IF;If i = J - 6 THENset @Ch2 = '万';set Zero = 0;end IF;if i = J - 2 THENset @Ch2 = '元';set Zero = 0;end IF;If i = J THENset @Ch2 = '整';end IF ;SET @ReturnValue = concat(@ReturnValue,@Ch1,@Ch2);SET i = i+1;end while;If LOCATE('仟仟',@ReturnValue) <> 0 thenset @ReturnValue = Replace(@ReturnValue, '仟仟', '仟');end if;If LOCATE('佰佰',@ReturnValue) <> 0 thenset @ReturnValue = Replace(@ReturnValue, '佰佰', '佰');end if;If LOCATE('零元',@ReturnValue) <> 0 thenset @ReturnValue = Replace(@ReturnValue, '零元', '元');end if;If LOCATE('零万',@ReturnValue) <> 0 thenset ReturnValue = Replace(@ReturnValue, '零万', '万');end if;If LOCATE('零亿',@ReturnValue) <> 0 thenset @ReturnValue = Replace(@ReturnValue, '零亿', '亿');end if;If LOCATE('零整',@ReturnValue) <> 0 thenset @ReturnValue = Replace(@ReturnValue, '零整', '整');end if;If LOCATE('零佰',@ReturnValue) <> 0 thenset @ReturnValue = Replace(@ReturnValue, '零佰', '零');end if;If LOCATE('零仟',@ReturnValue) <> 0 thenset @ReturnValue = Replace(@ReturnValue, '零仟', '零');end if;If LOCATE('元元',@ReturnValue) <> 0 thenset @ReturnValue = Replace(@ReturnValue, '元元', '元');end if;return @ReturnValue;end;

MySQL 的语法和 MS SQL有些不一样。
你可以参考一下ＭＹＳＱＬ的语法手册改一下。结构上基本类似。MySQL官方文档 http://dev.mysql.com/doc/refman/5.1/zh/index.html

搞定了
重新写了一个！
CREATE DEFINER=`root`@`%` FUNCTION `sp_cn`(n_LowerMoney decimal(15,2)) RETURNS varchar(200) CHARSET utf8
beginDeclare 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 ) ) doset 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_IWHEN 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)) thenset v_UpperStr = substring(v_UpperStr,2,(char_length(v_UpperStr) - 1));end if;if ( '零' = substring(v_UpperStr,1,1)) thenset v_UpperStr = substring(v_UpperStr,2,(char_length(v_UpperStr) - 1)) ;end if;if ( '角' = substring(v_UpperStr,1,1)) thenset v_UpperStr = substring(v_UpperStr,2,(char_length(v_UpperStr) - 1)) ;end if;if ( '分' = substring(v_UpperStr,1,1)) thenset v_UpperStr = substring(v_UpperStr,2,(char_length(v_UpperStr) - 1)) ;end if;if ('整' = substring(v_UpperStr,1,1)) thenset v_UpperStr = '零元整' ;end if;return v_UpperStr;END;

调试易

实现阿拉伯数字转为中文的MYsql函数

解决方案 »