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.
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.
