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

Question

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

Answer 1

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.

Answer 2

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    |

Answer 3

Using SUBSTRING_INDEX function:

SELECT 
  TRIM(SUBSTRING_INDEX(SUBSTRING_INDEX(names,',',x.x),',',-1)) as name
FROM mytable
CROSS JOIN (SELECT 1 as x union all select 2 union all select 3 union all select 4) x
WHERE x.x <= LENGTH(names)-LENGTH(REPLACE(names,',',''))+1

A (short) explanation:

The function SUBSTRING_INDEX(names,',',2) will return the first two names in the string names. When using -1 for the last parameter, you will only get the last element of this string, so you will end up with the second name in names.

The CROSS JOIN in this simple example limits the number of names to 4 per record. When you need more names, this should be expanded or a reference to a table with just integers can be used.

see: DBFIDDLE

Answer 4

A little bit tweaked version of above answers:

create or replace procedure splitstring(
    value TEXT,
    delim VARCHAR(12)
)
BEGIN 
    with recursive
    data as (select CONCAT(value,delim) as entries),
    cte as (
        select
            substring(entries, 1, locate(delim, entries) - LENGTH(delim)) entry,
            substring(entries, locate(delim, entries) + LENGTH(delim)) entries
        from data
        union all
        select 
            substring(entries, 1, locate(delim, entries) - LENGTH(delim)) entry,
            substring(entries, locate(delim, entries) + LENGTH(delim)) entries
        from cte
        where locate(delim, entries) > 0
    )
select entry from cte where length(entry) > 0;
END

Usage

call splitrec('Alex,Stitch,Cain,Denton', ',')

• Gives a nicely readable and usable table.
• Handles empty strings, string without delimeter present, and nulls.
• Can take in any delimeter (of 12 chars length max, which can be tweaked if needed).
• Does not respect whitespaces in the string, which can be ammended by

select REPLACE(REPLACE(REPLACE(very_messy_string_value, ' ', ''), '\t', ''), '\n', '');