MySQL - Explode CSV String into rows



 
    CREATE TABLE table1( names varchar(255) not null default '');
    INSERT INTO table1 VALUES('bob,jim,larry'),('sam,jesse'),('fizz');
    +---------------+
    | names         |
    +---------------+
    | bob,jim,larry |
    | sam,jesse     |
    | fizz          |
    +---------------+
 
    SELECT TRIM(SUBSTRING_INDEX(SUBSTRING_INDEX(table1.names, ',', j.whichval), ',', -1)) as exploded
    FROM table1 
     JOIN (SELECT '1' as whichval 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'  ) as j
    WHERE 1 
     AND j.whichval<= 1+LENGTH(table1.names) - LENGTH(REPLACE(table1.names,',',''))
 
    +----------+
    | exploded |
    +----------+
    | bob      |
    | sam      |
    | fizz     |
    | jim      |
    | jesse    |
    | larry    |
    +----------+
The only constraint here is... I did (select '1' union '2'...'10') so this only works up to 10 comma delimited values in a column. So if you have more comma delimited values in your column you need to add more numbers to union (.. union 11 union 12 etc.).



code snippets are licensed under Creative Commons CC-By-SA 3.0 (unless otherwise specified)