Split separated data by comma into multiple rows

Question

I want to modify table in my production server by extracting some column and put it into another table, the problem is the old column has multiple value separated by commas and in new table will be put in multiple records by query. i know the way like INSERT INTO user_emails(user_id, email) SELECT id, email FROM users but it would copy entire content of field.

table users (old)
-----------------------------------------------------
id  name  emails (removed)
-----------------------------------------------------
1   A     aaa@email.com, bbb@email.com
2   B     ccc@email.com, ddd@email.com, eee@email.com

table user_emails (new table)
---------------------------------
id  user_id  email
---------------------------------
1   1        aaa@email.com
2   1        bbb@email.com
3   2        ccc@email.com
4   2        ddd@email.com
5   2        eee@email.com

Any solution? Thank you

This should convince you why [you should not store comma-separated lists of values](https://stackoverflow.com/questions/3653462/is-storing-a-delimited-list-in-a-database-column-really-that-bad/3653574#3653574), if you actually want SQL to work with the individual elements of the lists. — Bill Karwin, Nov 01 '17 at 06:38
I know, it's a bad practice, above is example data, before we store unimportant data such a message description but now we need in proper table master-detail so we can easily to manage that. — Angga Ari Wijaya, Nov 01 '17 at 07:14
@billkarwin it seems like they’re trying to fix that issue — Strawberry, Nov 01 '17 at 07:57

score 1 · Answer 1 · answered Nov 01 '17 at 06:37

you should get fetch data from DB and store into array and then use explode() function to split the data

<?php
   $data=explode("," , $allEmails);
?>

now $data is convert into array like this

Array ( [0] => abc@g.com [1] => a@yahoo.com [2] => z@y.com )

finally you can insert into new table using insert query

Split separated data by comma into multiple rows

1 Answers1