How to split multi-valued comma separated values into individual row in mysql query

Question

I have a table users having column name email where multiple email id are stored as below

UserID|Email
---------------
1 | [email protected],[email protected],[email protected]
2 | [email protected],[email protected]

I'm trying to write a MySQL query to get result in the form

[email protected]
[email protected]
[email protected]
[email protected]
[email protected]

I'm not allowed to change the DB design or create new table. Can someone please help me figure out if this can be done in MySQL query or not.

Thanks

question from:https://stackoverflow.com/questions/65935375/how-to-split-multi-valued-comma-separated-values-into-individual-row-in-mysql-qu

Answer 1

SELECT DISTINCT UserID, SUBSTRING_INDEX(SUBSTRING_INDEX(Email, ',', num), ',', -1) email
FROM users
JOIN ( SELECT 1 num UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5
       UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9 UNION SELECT 10 ) nums