Mysql string splitting

Question

Possible duplicate:
Can Mysql split a column?

I have one string column and one comma separated column as below

Example, I have a table,

Col1 | col2 1 | a,b,c 2 | d,e 

I want top

 Col1,Col2 1 ,a 1 ,b 1 ,c 2 ,d 2 ,e 

How to achieve this

This is not an exact duplicate.

Answer 1

You can do this using a stored procedure.

 DELIMITER $$ CREATE FUNCTION strSplit(x VARCHAR(65000), delim VARCHAR(12), pos INTEGER) RETURNS VARCHAR(65000) BEGIN DECLARE output VARCHAR(65000); SET output = REPLACE(SUBSTRING(SUBSTRING_INDEX(x, delim, pos) , LENGTH(SUBSTRING_INDEX(x, delim, pos - 1)) + 1) , delim , ''); IF output = '' THEN SET output = null; END IF; RETURN output; END $$ CREATE PROCEDURE BadTableToGoodTable() BEGIN DECLARE i INTEGER; SET i = 1; REPEAT INSERT INTO GoodTable (col1, col2) SELECT col1, strSplit(col2, ',', i) FROM BadTable WHERE strSplit(col2, ',', i) IS NOT NULL; SET i = i + 1; UNTIL ROW_COUNT() = 0 END REPEAT; END $$ DELIMITER ;