Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

MySQL: Split comma separated list into multiple rows

I have an unnormalized table with a column containing a comma separated list that is a foreign key to another table:

+----------+-------------+   +--------------+-------+
| part_id  | material    |   | material_id  | name  |
+----------+-------------+   +--------------+-------+
|      339 | 1.2mm;1.6mm |   |            1 | 1.2mm |
|      970 | 1.6mm       |   |            2 | 1.6mm |
+----------+-------------+   +--------------+-------+

I want to read this data into a search engine that offers no procedural language.

So is there a way to either make a join on this column or run a query on this data that inserts appropriate entries into a new table? The resulting data should look like this:

+---------+-------------+
| part_id | material_id |
+---------+-------------+
|     339 |           1 |
|     339 |           2 |
|     970 |           2 |
+---------+-------------+

I could think of a solution if the DBMS supported functions returning a table but MySQL apparently doesn't.

like image 369
AndreKR Avatar asked Oct 14 '10 17:10

AndreKR


People also ask

How do I split a single string into multiple rows in SQL?

The SplitString function 1. @Input – The string to be split. 2. @Character – The character that is used to separate the words or letters.

How split multiple comma separated values in SQL query?

Split comma-separated value string in a column. SELECT ProductId, Name, value FROM Product CROSS APPLY STRING_SPLIT(Tags, ','); Here is the result set.


1 Answers

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

like image 170
Amit Avatar answered Oct 13 '22 06:10

Amit