MySQL 提取字符和数字里面的数字

谁借莪１个温暖的怀抱￠ 2022-12-14 15:53 97阅读 0赞

备注:测试数据库版本为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)