mysql的函数
实现
阿拉伯数字转为中文
实现
阿拉伯数字转为中文
解决方案 »
- 如何远程下载mysql数据文件--新手救助
- 菜鸟请问mysql中间隔号的作用
- 如何实现text字段的到达一定长度比如(255)之后自动截断
- 请教一条SQL (统计出订单上的总金额和总赠送的积分)
- 郁闷啊,MySql在DOS下用Localhost连接就没问题,用IP地址或电脑名称都不行,急,在线等啊!!!
- Mysql4.1.0的连接问题
- 请问:linux下的MD5校验码是什么东东,在安装软件时如何使用?
- 从mysql导出数据库.sql文件时,报1146 - Table ‘mysql.event’ doesn`t exist 错误
- 我只是想知道 Player表里 serverid=0 的数据是否至少有一条, 怎样写sql 效率高
- mysql更新触发器先插入另一张表然后删除该条数据
- 求数据库设计 24小时内最高点击 和 周最高点击
- 用DOS將csv 格式 直接匯入 mysql 資料庫
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)
得出
“壹百贰拾叁圆肆角伍分”
的结果;
求这样的存储过程!
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的语法手册改一下。结构上基本类似。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;