MySQL: Split comma separated list into multiple rows

Question

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.

Answer 1

This might help get you started, it splits a field's value and places the values in a temporary table.

http://forge.mysql.com/tools/tool.php?id=4 (click on the Show Highlighted Code tab)

Answer 2

I've answered two similar questions in as many days but not had any responses so I guess people are put off by the use of the cursor but as it should be a one off process I personally dont think that matters.

As you stated MySQL doesnt support table return types yet so you have little option other than to loop the table and parse the material csv string and generate the appropriate rows for part and material.

The following posts may prove of interest:

http://stackoverflow.com/questions/3928325/split-keywords-for-post-php-mysql/3929161#3929161

http://stackoverflow.com/questions/3908966/mysql-procedure-to-load-data-from-staging-table-to-other-tables-need-to-split-up/3909888#3909888

Rgds