mysql 字符串 子串_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)