Split values in SQL after a specific character

Question

I have a table with one column :

Val A
Val B
Val C,Val B,Val D
Val A,Val F,Val A

My question how can i split the values after a specific character in this case "," so that i can have only one per row like this :

Val A
Val B
Val C
Val B
Val D
Val A
Val F
Val A

I don't if it's important but i'm using MySql Workbench. Thanks in advance.

Hi @rzss275 I have attached the link with the answer I believe you need... — VBoka, Sep 17 '20 at 10:45
Does this answer your question? [how to create a query in sql to chop sentences into words and add them to new table with their frequency](https://stackoverflow.com/questions/61653010/how-to-create-a-query-in-sql-to-chop-sentences-into-words-and-add-them-to-new-ta) or [How to split values in single strings using comma](https://stackoverflow.com/a/62637258/10138734) — Akina, Sep 17 '20 at 10:47
Never, ever store data as comma separated items! It will only cause you lots of trouble. — jarlh, Sep 17 '20 at 10:48
@VBoka thanks for the link and for your help ,i found in there one solution that works for me. — rzss275, Sep 17 '20 at 11:21
@jarlh This is how it's ,it's not my decision to have data like that. — rzss275, Sep 17 '20 at 11:22
@rzss275 . . . I don't like the answers in the "duplicate". The recursive CTE below is probably the simplest solution for what you want. — Gordon Linoff, Sep 17 '20 at 12:31

Gordon Linoff · Answer 1 · 2020-09-17T12:31:24.890

You can use substring_index(). One method is:

select substring_index(col, ';', 1)
from t
union all
select substring_index(substring_index(col, ';', 2), ';', -1)
from t
where col like '%;%'
union all
select substring_index(substring_index(col, ';', 3), ';', -1)
from t
where col like '%;%;%';

You need to add a separate subquery up to the maximum number of elements in any row.

EDIT:

I don't really like the answers in the duplicate. I would recommend a recursive CTE:

with recursive cte as (
      select col as part, concat(col, ',') as rest, 0 as lev
      from t
      union all
      select substring_index(rest, ',', 1),
             substr(rest, instr(rest, ',') + 1),
             lev + 1
      from cte
      where rest <> '' and lev < 5 
     )
select part
from cte
where lev > 0;

Here is a db<>fiddle.

That would be a problem because there is a lot of data and i don't the maximum number of elements in a row. — rzss275, Sep 17 '20 at 11:24

Split values in SQL after a specific character

1 Answers1