SQL (Maria DB) split string separated by comma to rows

user_14488613 0
Viewed 3033

I have this column:

names
John, Mary
Joseph
Eleanor, Sophia, Dani

And I want this output:

names
John
Mary
Joseph
Eleanor
Sophia
Dani

And it should include the SUBSTRING_INDEX function

mariadb recursive-query csv sql string
2 Answers

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;

Here is a db<>fiddle.

user_11440350

One option uses a recursive query:

 with recursive
    data as (select concat(names, ', ') names from mytable),
    cte as (
        select 
            substring(names, 1, locate(', ', names) - 1) word,
            substring(names, locate(', ', names) + 2) names
        from data
        union all
        select 
            substring(names, 1, locate(', ', names) - 1) word,
            substring(names, locate(', ', names) + 2) names
        from cte
        where locate(', ', names) > 0
    )
select word from cte

Demo on DB Fiddle:

| word    |
| :------ |
| John    |
| Joseph  |
| Eleanor |
| Mary    |
| Sophia  |
| Dani    |
user_106767160
Related