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