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

```html

One option uses a recursive query:

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

Demo on DB Fiddle:

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

Another answer

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),
             substring(names, instr(names, ',') + 2), lev + 1
      from cte
      where instr(names, ',') > 0
     )
select name
from cte
where lev > 1;

Here is a dbfiddle.

Another answer

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

Usage

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

• Gives a 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 spaces in the string, which can be ammended by

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

Another answer

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

```