Parse a string into columns non-procedurally

from the Artful MySQL Tips List


Even jaded SQLers might tend to approach this puzzle procedurally, but we can use MySQL's Group_Concat() function to massage a CSV list into a Insert ... Values ... list:


drop table if exists t;

create table t( txt text );

insert into t values('1,2,3,4,5,6,7,8,9');



drop temporary table if exists temp;

create temporary table temp( val char(255) );

set @sql = concat("insert into temp (val) values ('", replace(( select group_concat(distinct txt) as data from t), ",", "'),('"),"');");

prepare stmt1 from @sql;

execute stmt1;

select distinct(val) from temp;

+------+

| val  |

+------+

| 1    |

| 2    |

| 3    |

| 4    |

| 5    |

| 6    |

| 7    |

| 8    |

| 9    |

+------+




Return to the Artful MySQL Tips page