MySQL 提取第n个分隔的子串

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