备注:测试数据库版本为MySQL 8.0
一.需求
测试数据准备:
mysql> create table test1(id int,str varchar(100));
Query OK, 0 rows affected (0.02 sec)
mysql> insert into test1 values (1,'abc123'),(2,'123abc'),(3,'1abc23'),(4,'abc'),(5,'123');
Query OK, 5 rows affected (0.01 sec)
Records: 5 Duplicates: 0 Warnings: 0
mysql> select * from test1;
+------+--------+
| id | str |
+------+--------+
| 1 | abc123 |
| 2 | 123abc |
| 3 | 1abc23 |
| 4 | abc |
| 5 | 123 |
+------+--------+
5 rows in set (0.00 sec)
需要提取字符和数字里面的数字
二.解决方案
遍历MySQL字符，然后取出其中的数字
代码:
select id,cast(group_concat(c order by pos separator '') as unsigned)
as mixed1
from (
select v.id , v.str, iter.pos,substr(v.str,iter.pos,1) as c
from test1 v,
( select id pos from t10 ) iter
where iter.pos <= length(v.str)
and ascii(substr(v.str,iter.pos,1)) between 48 and 57
) y
group by id,str
order by 1
测试记录:
mysql> select * from t10;
+------+
| id |
+------+
| 1 |
| 2 |
| 3 |
| 4 |
| 5 |
| 6 |
| 7 |
| 8 |
| 9 |
| 10 |
+------+
10 rows in set (0.00 sec)
mysql> select id,cast(group_concat(c order by pos separator '') as unsigned)
-> as mixed1
-> from (
-> select v.id , v.str, iter.pos,substr(v.str,iter.pos,1) as c
-> from test1 v,
-> ( select id pos from t10 ) iter
-> where iter.pos <= length(v.str)
-> and ascii(substr(v.str,iter.pos,1)) between 48 and 57
-> ) y
-> group by id,str
-> order by 1;
+------+--------+
| id | mixed1 |
+------+--------+
| 1 | 123 |
| 2 | 123 |
| 3 | 123 |
| 5 | 123 |
+------+--------+
4 rows in set (0.00 sec)
