MySQL: Split Comma Separated List into Multiple Rows

SQL split values to multiple rows

If you can create a numbers table, that contains numbers from 1 to the maximum fields to split, you could use a solution like this:

select
  tablename.id,
  SUBSTRING_INDEX(SUBSTRING_INDEX(tablename.name, ',', numbers.n), ',', -1) name
from
  numbers inner join tablename
  on CHAR_LENGTH(tablename.name)
     -CHAR_LENGTH(REPLACE(tablename.name, ',', ''))>=numbers.n-1
order by
  id, n

Please see fiddle here.

If you cannot create a table, then a solution can be this:

select
  tablename.id,
  SUBSTRING_INDEX(SUBSTRING_INDEX(tablename.name, ',', numbers.n), ',', -1) name
from
  (select 1 n union all
   select 2 union all select 3 union all
   select 4 union all select 5) numbers INNER JOIN tablename
  on CHAR_LENGTH(tablename.name)
     -CHAR_LENGTH(REPLACE(tablename.name, ',', ''))>=numbers.n-1
order by
  id, n

an example fiddle is here.

Split comma separated value in to multiple rows in mysql

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 string into rows in mysql

Use a subquery of arbitrary digits to split your string.Instead of vals you can use '1,2,3'.

SELECT
  DISTINCT SUBSTRING_INDEX(SUBSTRING_INDEX(vals, ',', n.digit+1), ',', -1) val
FROM
  tt1
  INNER JOIN
  (SELECT 0 digit UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3  UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6) n
  ON LENGTH(REPLACE(vals, ',' , '')) <= LENGTH(vals)-n.digit;

See it working

how to split single row to multiple rows in mysql

We can use a cross/inner join approach here with the help of SUBSTRING_INDEX():

SELECT
    t1.datetime1,
    t1.count,
    t1.num1,
    t2.num2
FROM
(
    SELECT datetime1, count, SUBSTRING_INDEX(num1, ',', 1) AS num1
    FROM yourTable
    UNION ALL
    SELECT datetime1, count, SUBSTRING_INDEX(num1, ',', -1)
    FROM yourTable
) t1
INNER JOIN
(
    SELECT datetime1, count, SUBSTRING_INDEX(num2, ',', 1) AS num2
    FROM yourTable
    UNION ALL
    SELECT datetime1, count, SUBSTRING_INDEX(num2, ',', -1)
    FROM yourTable
) t2
    ON t2.datetime1 = t1.datetime1
ORDER BY
    t1.datetime1,
    t1.num1,
    t2.num2;

Demo

MySQL: Split comma separated list into multiple rows

In MySQL this can be achieved as below

SELECT id, length FROM vehicles WHERE id IN ( 117, 148, 126) 

+---------------+
| id  | length  |
+---------------+
| 117 | 25      |
| 126 | 8       |
| 148 | 10      |
+---------------+

SELECT id,vehicle_ids FROM load_plan_configs WHERE load_plan_configs.id =42

+---------------------+
| id  | vehicle_ids   |
+---------------------+
| 42  | 117, 148, 126 |
+---------------------+

Now to get the length of comma separated vehicle_ids use below query

Output

SELECT length 
FROM   vehicles, load_plan_configs   
WHERE  load_plan_configs.id = 42 AND FIND_IN_SET(
       vehicles.id, load_plan_configs.vehicle_ids
)

+---------+
| length  |
+---------+
| 25      |
| 8       |
| 10      |
+---------+

For more info visit http://amitbrothers.blogspot.in/2014/03/mysql-split-comma-separated-list-into.html

MySQL Split 2 comma separated columns into multiple rows with lookup

Yes, but you won't be happy with the performance.

You can match a comma-separated list against an individual value using MySQL's FIND_IN_SET() function.

select p.alias, a.category as group_1, c.country as group_2 
from myProducts p join myCategories a on find_in_set(a.id, p.group_1) 
join countries c on find_in_set(c.country, p.group_2);

+-----------+------------+---------+
| alias     | group_1    | group_2 |
+-----------+------------+---------+
| product_c | category_a | spain   |
| product_c | category_d | spain   |
| product_a | category_a | uk      |
| product_a | category_b | uk      |
| product_b | category_b | uk      |
| product_a | category_c | uk      |
| product_b | category_d | uk      |
+-----------+------------+---------+

I did create another lookup table countries:

create table countries (country varchar(20) primary key);
insert into countries values ('uk'),('us'),('spain'),('germany');

Caveat: if the comma-separated list has spaces, they will be treated as part of each string in the list, so you want to remove spaces.

select p.alias, a.category as group_1, c.country as group_2 
from myProducts p join myCategories a on find_in_set(a.id, p.group_1) 
join countries c on find_in_set(c.country, replace(p.group_2,' ',''));

+-----------+------------+---------+
| alias     | group_1    | group_2 |
+-----------+------------+---------+
| product_c | category_a | germany |
| product_c | category_d | germany |
| product_c | category_a | spain   |
| product_c | category_d | spain   |
| product_a | category_a | uk      |
| product_a | category_b | uk      |
| product_b | category_b | uk      |
| product_a | category_c | uk      |
| product_b | category_d | uk      |
| product_b | category_b | us      |
| product_b | category_d | us      |
+-----------+------------+---------+

But there's no way to optimize the lookups with indexes if you do this. So every join will be a table-scan. As your tables gets larger, you'll find the performance degrades to the point of being unusable.

The way to optimize this is to avoid using comma-separated lists. Normalize many-to-many relationships into new tables. Then the lookups can use indexes, and you'll avoid the degraded performance, in addition to all the other problems with using comma-separated lists.

---

Re your comment:

You can create a derived table by listing countries explicitly:

FROM ...
JOIN (
  SELECT 'us' AS country UNION SELECT 'uk' UNION SELECT 'spain' UNION SELECT 'germany'
) AS c

But this is getting pretty ridiculous. You aren't using SQL to any advantage. You might as well just fetch the whole dataset back into your client application and sort it into some data structures in memory.

Split value into multiple rows

You are on the right track with the table of numbers. You should start by adding more rows, so it matches (or exceeds) the maximum possible number of elements in a CSV list.

Then, you can use a join condition to generate the relevant number of rows per name only

select
    t.*,
    substring_index(substring_index(t.name, ';', n.n), ';', -1) name
from numbers n
inner join testLocation t   
    on n <= length(t.name) - length(replace(t.name, ';', '')) + 1

Demo on DB Fiddle (I expanded the numbers to 8):

id | State   | name                                    | name        
-: | :------ | :-------------------------------------- | :-----------
 1 | Alabama | Birmingham;Huntsville;Mobile;Montgomery | Birmingham  
 1 | Alabama | Birmingham;Huntsville;Mobile;Montgomery | Huntsville  
 1 | Alabama | Birmingham;Huntsville;Mobile;Montgomery | Mobile      
 1 | Alabama | Birmingham;Huntsville;Mobile;Montgomery | Montgomery  
 2 | Florida | Tampa;Jacksonville;Destin               | Tampa       
 2 | Florida | Tampa;Jacksonville;Destin               | Jacksonville
 2 | Florida | Tampa;Jacksonville;Destin               | Destin      

Note, that, as commented already by others, storing CSV lists in a database is bad practice and should almost always be avoided. Recommended related reading: Is storing a delimited list in a database column really that bad?

---

Related Topics