Split comma separated value in to multiple rows in mysql

Question

I have a table like this and consider
table name as sample

   info | values
  -------------
   1     1,2,4,5
   2     5, 6,7

When i select the sample table with info value as 1 instead of getting comma separated value is there any way to split the value and return it as multiple rows

    info | values
     1      1
     1      2
     1      4
     1      5

If I get this I will take this as subquery and pass it to where in cluase to fetch other data from other table.

Why dont you do it in your server side programming language ? — Indra Kumar S, Jun 15 '21 at 14:41
Yeah but this is report query and I need to pass this values as input for another table where in clause .. since it is very huge data we want to filter it from db — Surya Prakash Tumma, Jun 15 '21 at 14:42
`very huge` - While Dealing with big tables You should learn how to simplify queries. The one you asked for ..is going to complicate the things — Indra Kumar S, Jun 15 '21 at 14:44
and one more reason As I am beginner in mysql I am very curious to know how to write this query — Surya Prakash Tumma, Jun 15 '21 at 14:47
Well answer is given below. But i wont recommend the answered query as subquery for your need — Indra Kumar S, Jun 15 '21 at 14:48
in the below answer I have some concerns what if the values are not numbers if they are words and even I cant pass it as subquery.. if my max is more — Surya Prakash Tumma, Jun 15 '21 at 14:50
This was closed as a duplicate of https://stackoverflow.com/q/3653462/20860. While it is a good idea to avoid comma-separated lists in relational database design, that's not a duplicate of this question, and does not answer the OP's question. — Bill Karwin, Jun 15 '21 at 15:07

score 1 · Accepted Answer · answered Jun 15 '21 at 14:37

If you know the maximum number, you can use a bunch of union alls. For your sample data, this is sufficient:

select col1, substring_index(col2, ',', 1)
from t
union all
select col1, substring(substring_index(col2, ',', 2), ',', -1)
from t
where col2 like '%,%'
union all
select col1, substring(substring_index(col2, ',', 3), ',', -1)
from t
where col2 like '%,%,%'
union all
select col1, substring(substring_index(col2, ',', 4), ',', -1)
from t
where col2 like '%,%,%,%';

Split comma separated value in to multiple rows in mysql

1 Answers1