我想要实现输入一个字符串'a345bc6d'分割成‘abcd’和‘3456’
显示为
name 字符 数字
a345bc6d abcd 3456
所以我写了一个存储过程
begin
set @var:=0;
create table t
(id integer );
while (@var:=@var+1)<=20 do
insert into t values (@var);
end while;
select one,字母,数字 from
(select one, group_concat(substring(one,pos,1) separator '') as '字母'
from (select id pos from t ) a
where pos<=length(one)
and not find_in_set(substring(one,pos,1),'0,1,2,3,4,5,6,7,8,9')) t1,
(select one, group_concat(substring(one,pos,1) separator '') as '数字'
from (select id pos from t ) b
where pos<=length(one)
and find_in_set(substring(one,pos,1),'0,1,2,3,4,5,6,7,8,9')) t2
where t1.one=t2.one;
drop table t;
end
表我原来用的是临时表t报的错时can‘t reopen table t 我改成成了不是临时表,还好是有这个错误,
表不能查询多次吗????
另外有没有什么好的别的办法来解决这个问题???
显示为
name 字符 数字
a345bc6d abcd 3456
所以我写了一个存储过程
begin
set @var:=0;
create table t
(id integer );
while (@var:=@var+1)<=20 do
insert into t values (@var);
end while;
select one,字母,数字 from
(select one, group_concat(substring(one,pos,1) separator '') as '字母'
from (select id pos from t ) a
where pos<=length(one)
and not find_in_set(substring(one,pos,1),'0,1,2,3,4,5,6,7,8,9')) t1,
(select one, group_concat(substring(one,pos,1) separator '') as '数字'
from (select id pos from t ) b
where pos<=length(one)
and find_in_set(substring(one,pos,1),'0,1,2,3,4,5,6,7,8,9')) t2
where t1.one=t2.one;
drop table t;
end
表我原来用的是临时表t报的错时can‘t reopen table t 我改成成了不是临时表,还好是有这个错误,
表不能查询多次吗????
另外有没有什么好的别的办法来解决这个问题???
If you refer to a temporary table multiple times in a stored function under different aliases, a Can't reopen table: 'tbl_name' error occurs, even if the references occur in different statements within the function.
建议在你的程序中处理而不是在数据库中处理。下面做了个例子,但不推荐。mysql> delimiter //
mysql> CREATE PROCEDURE p (IN sName varchar(20),OUT sAlpha varchar(20),OUT sDigit varchar(20))
-> BEGIN
-> DECLARE i INT DEFAULT 0;
-> DECLARE c CHAR;
->
-> set sAlpha='';
-> set sDigit='';
-> while i<length(sName) do
-> set i=i+1;
-> set c = SUBSTRING(sName,i,1);
-> if ASCII(c) between ASCII('0') and ASCII('9') then
-> set sDigit= concat(sDigit,c);
-> else
-> set sAlpha= concat(sAlpha,c);
-> end if;
-> end while;
-> END;
-> //
Query OK, 0 rows affected (0.00 sec)mysql> delimiter ;
mysql>
mysql> call p ('a345bc6d',@a,@b);
Query OK, 0 rows affected (0.00 sec)mysql> select @a,@b;
+------+------+
| @a | @b |
+------+------+
| abcd | 3456 |
+------+------+
1 row in set (0.00 sec)mysql>