BGJwDZE9

1DECLARE id INT DEFAULT 0;
2DECLARE value TEXT;
3DECLARE occurance INT DEFAULT 0;
4DECLARE i INT DEFAULT 0;
5DECLARE splitted_value INT;
6DECLARE done INT DEFAULT 0;
7DECLARE cur1 CURSOR FOR SELECT table1.id, table1.value
8                                     FROM table1
9                                     WHERE table1.value != '';
10DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;
11
12DROP TEMPORARY TABLE IF EXISTS table2;
13CREATE TEMPORARY TABLE table2(
14`id` INT NOT NULL,
15`value` VARCHAR(255) NOT NULL
16) ENGINE=Memory;
17
18OPEN cur1;
19  read_loop: LOOP
20    FETCH cur1 INTO id, value;
21    IF done THEN
22      LEAVE read_loop;
23    END IF;
24
25    SET occurance = (SELECT LENGTH(value)
26                             - LENGTH(REPLACE(value, bound, ''))
27                             +1);
28    SET i=1;
29    WHILE i <= occurance DO
30      SET splitted_value =
31      (SELECT REPLACE(SUBSTRING(SUBSTRING_INDEX(value, bound, i),
32      LENGTH(SUBSTRING_INDEX(value, bound, i - 1)) + 1), ',', ''));
33
34      INSERT INTO table2 VALUES (id, splitted_value);
35      SET i = i + 1;
36
37    END WHILE;
38  END LOOP;
39
40  SELECT * FROM table2;
41CLOSE cur1;