A Little Noise

20Nov/090

GROUP UNCONCAT

Dunno why you'd rather do this in SQL than in your application layer, but if you do, here's one way to turn a delimited string of values back into multiple rows - just the opposite of GROUP_CONCAT:

SET @sourceString = 'a,b,c,d,e';
SET @sql = CONCAT('INSERT INTO t VALUES (\\'', REPLACE(@sourceString, ',', '\\'),(\\''), '\\')');
PREPARE myStmt FROM @sql;
EXECUTE myStmt;

Just to show what's going on:

mysql> SELECT @sql;
+----------------------------------------------------+
| @sql                                               |
+----------------------------------------------------+
| INSERT INTO t VALUES ('a'),('b'),('c'),('d'),('e') |
+----------------------------------------------------+
Filed under: MySQL Leave a comment
Comments (0) Trackbacks (0)

No comments yet.


Leave a comment

This site uses Akismet to reduce spam. Learn how your comment data is processed.

No trackbacks yet.