MySQL 提取第n个分隔的子串

迷南。 2022-12-16 06:40 73阅读 0赞

备注:测试数据库版本为MySQL 8.0

# 一.需求 #

数据准备

create view v5 as
    select 'mo,larry,curly' as name
    union all
    select 'tina,gina,jaunita,regina,leena' as name;
    
    mysql> select * from v5;
    +--------------------------------+
    | name                           |
    +--------------------------------+
    | mo,larry,curly                 |
    | tina,gina,jaunita,regina,leena |
    +--------------------------------+
    2 rows in set (0.00 sec)

需要取出航中的第二个姓名  
larry  
gina

# 二.解决方案 #

其实用substring\_index 即可

代码:

SELECT name, SUBSTRING_INDEX(name,',',2) name1, SUBSTRING_INDEX(SUBSTRING_INDEX(name,',',2),',',-1) name2
    FROM v5;

测试记录:

mysql> create view v5 as
        -> select 'mo,larry,curly' as name
        -> union all
        -> select 'tina,gina,jaunita,regina,leena' as name;
    Query OK, 0 rows affected (0.01 sec)
    
    mysql>
    mysql> select * from v5;
    +--------------------------------+
    | name                           |
    +--------------------------------+
    | mo,larry,curly                 |
    | tina,gina,jaunita,regina,leena |
    +--------------------------------+
    2 rows in set (0.00 sec)
    
    mysql>
    mysql>
    mysql> SELECT name, SUBSTRING_INDEX(name,',',2) name1, SUBSTRING_INDEX(SUBSTRING_INDEX(name,',',2),',',-1) name2
        -> FROM v5;
    +--------------------------------+-----------+-------+
    | name                           | name1     | name2 |
    +--------------------------------+-----------+-------+
    | mo,larry,curly                 | mo,larry  | larry |
    | tina,gina,jaunita,regina,leena | tina,gina | gina  |
    +--------------------------------+-----------+-------+
    2 rows in set (0.00 sec)