6.13 判别可作为数值的字符串

最新推荐文章于 2022-11-05 16:23:54 发布

weixin_34133829

最新推荐文章于 2022-11-05 16:23:54 发布

阅读量63

点赞数

CC 4.0 BY-SA版权

原文链接：http://www.cnblogs.com/liang545621/p/7523177.html

问题：如果数值与字符混合在一起，需要删除那些字符，只返回数字。

create view v as
select concat(
substr(ename,1,2),
replace(cast(deptno as char(4)),' ',''),
substr(ename,3,2)
) as mixed
from emp
where deptno=10
union all
select replace(cast(empno as char(4)),' ','')
from emp where deptno=20
union all
select ename from emp where deptno=30;

select * from v;

解决方案：

select cast(group_concat(c order by pos separator '' ) as unsigned) as MIXED1
from (
select v.mixed,iter.pos,substr(v.mixed,iter.pos,1) as c
from v,
(select id pos from t10)iter
where iter.pos <=length(v.mixed)
and ascii(substr(v.mixed,iter.pos,1)) between 48 and 57
)y
group by mixed
order by 1;

+--------+
| MIXED1 |
+--------+
|     10 |
|     10 |
|     10 |
+--------+

转载于:https://www.cnblogs.com/liang545621/p/7523177.html