SQL(Maria DB)将用逗号分隔的字符串拆分为行 [英] SQL (Maria DB) split string separated by comma to rows

问题描述

我有这个专栏:

names
John, Mary
Joseph
Eleanor, Sophia, Dani

我想要这个输出:

names
John
Mary
Joseph
Eleanor
Sophia
Dani

它应该包括 SUBSTRING_INDEX 函数

And it should include the SUBSTRING_INDEX function

推荐答案

你可以使用递归 CTE:

You can use a recursive CTE:

with recursive cte as (
      select '            ' as name, concat(names, ',') as names, 1 as lev
      from t
      union all
      select substring_index(names, ',', 1),
             substr(names, instr(names, ',') + 2), lev + 1
      from cte
      where names like '%,%'
     )
select name
from cte
where lev > 1;

这里是一个 db<>fiddle.

Here is a db<>fiddle.

这篇关于SQL(Maria DB)将用逗号分隔的字符串拆分为行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！