MLAgn847

1--Write a procedure in MySQL to split a column into rows using a delimiter.
2CREATE TABLE sometbl ( ID INT, NAME VARCHAR(50) );
3INSERT INTO sometbl VALUES (1, 'Smith'), (2, 'Julio|Jones|Falcons'), (3,'White|Snow'), (4, 'Paint|It|Red'), (5, 'Green|Lantern'), (6,'Brown|bag');
4CREATE PROCEDURE `getSometbl`(bound VARCHAR(255))
5BEGIN
6    DECLARE id INT DEFAULT 0;
7    DECLARE value VARCHAR(50);
8    DECLARE occurance INT DEFAULT 0;
9    DECLARE i INT DEFAULT 0;
10    DECLARE x INT DEFAULT 0;
11    DECLARE len INT DEFAULT 0;
12    DECLARE splitted_value VARCHAR(50);
13    DECLARE done INT DEFAULT 0;
14    DECLARE cur1 CURSOR FOR SELECT sometbl.id, sometbl.name
15                                         FROM sometbl
16                                         WHERE sometbl.name != '';
17    DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;
18    DROP TEMPORARY TABLE IF EXISTS table2;
19    CREATE TEMPORARY TABLE table2(
20        `id` INT NOT NULL,
21        `value` VARCHAR(50) NOT NULL
22    ) ENGINE=Memory;   
23    OPEN cur1;
24      read_loop: LOOP
25        FETCH cur1 INTO id, value;
26        IF done THEN
27          LEAVE read_loop;
28        END IF;
29        SET i = 1;
30        SET len = LENGTH(value);
31        SET occurance = (SELECT len - LENGTH(REPLACE(value, bound, '')) + 1);
32        WHILE i <= occurance DO
33          SET x = LENGTH(SUBSTRING_INDEX(value, bound, 1));
34          SET splitted_value = (SELECT SUBSTRING(value, 1, x));
35          SET i = i + 1;
36          SET value = SUBSTRING(value, x + 2, LENGTH(value) - x + 1);
37        END WHILE;
38      END LOOP;
39      SELECT * FROM table2;
40    CLOSE cur1;     
41END
42;
43CALL getSometbl('|');