Separating a comma separated string to a new table

Question

I inherited a project that has comma separated strings stored in a field called 'subsector' in a table named 'com_barchan_project'. I need to change this horrible design, since it's proving to be an issue trying to parse through this field. See HERE for the full story:

| id | name | sector | subsector |
+----+------+--------+-----------+
|  1 | test |   2    |  3,4,7    |
+----+------+--------+-----------+
|  2 | door |   5    |  2        |

I have created a new table called 'com_barchan_project_subsector_join' with the required fields and would like to move the values stored in 'com_barchan_project' to this new empty table.

Can anyone help me with the SQL statement that would accomplish this?

Here's what the new 'com_barchan_project_subsector_join' table should look like:

| id | project_id | subsector_id |
+----+------------+--------------+
|  1 |      1     |      3       |
+----+------------+--------------+
|  2 |      1     |      4       |
+----+------------+--------------+
|  3 |      1     |      7       |
+----+------------+--------------+
|  4 |      2     |      2       |

Once I move over the data, I will remove the 'subsector' field from the 'com_barchan_project' table and be done with it.

Thanks for your help!!!

John

Senior Web Developer doesn't mean I have all the answers. I'm not afraid to admit that this is not something I've had to do. Why the need to insult. Wow! Rude. — , May 31 '17 at 19:07
you should approach this problem server side .. using a procedural approach (not relational ... alias mysql based) — ScaisEdge, May 31 '17 at 19:10
https://stackoverflow.com/questions/17942508/sql-split-values-to-multiple-rows — Iłya Bursov, May 31 '17 at 19:17
https://stackoverflow.com/questions/19073500/sql-split-comma-separated-row — Iłya Bursov, May 31 '17 at 19:17
https://stackoverflow.com/questions/3936088/mysql-split-comma-separated-list-into-multiple-rows — Iłya Bursov, May 31 '17 at 19:18
https://stackoverflow.com/questions/40232800/how-to-split-up-a-comma-separated-table-into-multiple-rows-using-mysql — Iłya Bursov, May 31 '17 at 19:18
@Lashane: Thanks for the links. I looked at these, but they were not helpful in my case. Uueerdo had the correct solution below. — , May 31 '17 at 20:20

score 0 · Accepted Answer · answered May 31 '17 at 19:15

0

Using shorter table names for brevity/clarity; and assuming you have (or can easily make) a comprehensive subsectors table...and assuming your csv are stored in a consistent format (no spaces at least).

INSERT INTO `project_subsectors` (project_id, subsector_id)
SELECT p.id, s.id
FROM projects AS p
INNER JOIN subsectors AS s ON p.subsector = s.id
                           OR p.subsector LIKE CONCAT(s.id, ',%')
                           OR p.subsector LIKE CONCAT('%,', s.id, ',%')
                           OR p.subsector LIKE CONCAT('%,', s.id)
;

I can't guarantee it will be fast; I'd be surprised if it was.

ON FIND_IN_SET(s.id, p.subsector) > 0 may work as well, but I am not as familiar with the behavior of that function.

answered May 31 '17 at 19:15

Uueerdo

15,723
1
16
21

Thanks for your help. Your answer helped me get there. – May 31 '17 at 20:18
@Ueerdo: By the way, it was fast. – May 31 '17 at 20:21
@JohnLeger Was it a small table, or perhaps most rows only had single values in the field? _Join conditions with `OR` are usually relatively slow even without the overhead of LIKE comparisons and CONCAT() function calls._ – Uueerdo May 31 '17 at 20:29
There were about 800+ rows. I only needed – Jun 01 '17 at 20:38

Separating a comma separated string to a new table

1 Answers1