Mysql split

mysql_split_magic.sql

-- https://stackoverflow.com/a/22669310/1531963
-- Assuming that the comma separated list is in table data.list, 
-- and it contains listing of codes from other table classification.code, you can do something like:

SELECT 
    d.id, d.list, c.code
FROM 
    classification c
    JOIN data d
        ON d.list REGEXP CONCAT('[[:<:]]', c.code, '[[:>:]]');
        

split_explanation.txt

Found at https://stackoverflow.com/a/22669310/1531963


So if you have tables and data like this:

CLASSIFICATION (code varchar(4) unique): ('A'), ('B'), ('C'), ('D')
MY_DATA (id int, list varchar(255)): (100, 'C,A,B'), (150, 'B,A,D'), (200,'B')
above SELECT will return

(100, 'C,A,B', 'A'),
(100, 'C,A,B', 'B'),
(100, 'C,A,B', 'C'),
(150, 'B,A,D', 'A'),
(150, 'B,A,D', 'B'),
(150, 'B,A,D', 'D'),
(200, 'B', 'B'),