MySql parse, split in several rows without knowing the number of divitions

Question

Right now. I'm working migrating options from tableA to tableB:
tableA

╔═══════════╦══════════════╗
║ id_skills ║ skills       ║
╠═══════════╬══════════════╣
║ 1         ║ Java, Python ║
║ 2         ║ JavaScript   ║
║ 3         ║ Ruby, Java   ║
║ 4         ║ Python, C#   ║
║ 5         ║ C++, Ruby    ║
╚═══════════╩══════════════╝

tableB

╔═══════════╦════════════╗
║ id_skills ║ skills     ║
╠═══════════╬════════════╣
║ 1         ║ Java       ║
║ 2         ║ JavaScript ║
║ 3         ║ Ruby       ║
║ 4         ║ C#         ║
║ 5         ║ C++        ║
║ 6         ║ Python     ║
╚═══════════╩════════════╝

I was trying to find a function of sql, that unconcat a string
and is necessary to create everything in a script
We don't really know how many "," is going to be in each row.
Example:
"Java, Python"
In this case, into two rows:
-> Java
-> Python

Note:There isn't a function of MySql that split a string

possible duplicate of ["Reverse GROUP\_CONCAT" in MySQL?](http://stackoverflow.com/questions/17308669/reverse-group-concat-in-mysql) — Daniel A. White, Jan 18 '15 at 21:39

score 2 · Answer 1 · answered Jan 18 '15 at 21:53

You can do this in a query using substring_index(), if you know the maximum number of possible values in a string:

select (@rn := @rn + 1) as id_skills,
       substring_index(substring_index(a.skills, ', ', n.n), ', ', -1) as skill
from tableA a join
     (select 1 as n union all select 2 union all select 3 union all select 4
     ) n
     on n.n <= 1 + length(a.skills) - length(replace(a.skills, ',', '') cross join
     (select @rn := 0) vars;

You just need to be sure you have enough values in the n subquery.

Very straightforward way to do it. Thank you so much – ariel_556 Jan 18 '15 at 22:15 — ariel_556, Jan 18 '15 at 22:15

MySql parse, split in several rows without knowing the number of divitions

1 Answers1