Write a procedure in MySQL to split a column into rows using a delimiter.

Que:
Write a procedure in MySQL to split a column into rows using a delimiter.
CREATE TABLE sometbl ( ID INT, NAME VARCHAR(50) );
INSERT INTO sometbl VALUES (1, 'Smith'), (2, 'Julio|Jones|Falcons'), (3, 'White|Snow'), (4, 'Paint|It|Red'), (5, 'Green|Lantern'), (6, 'Brown|bag');
For (2), example rows would look like >> “3, white”, “3, Snow”

=============================================================
DELIMITER $$

-- Splits column value and add each splitted element as new
-- a new record to the table.
CREATE PROCEDURE splitted_table(delimeter VARCHAR(255))

BEGIN

    DECLARE id INT DEFAULT 0;
    DECLARE NAME VARCHAR(250);
    DECLARE occur INT DEFAULT 0;
    DECLARE i INT DEFAULT 0;
    DECLARE pipe INT DEFAULT 0;
    DECLARE splitted_name VARCHAR(50);
    DECLARE done INT DEFAULT 0;
    DECLARE sourceTable CURSOR FOR SELECT sometbl.id, sometbl.name FROM sometbl WHERE sometbl.name != '';
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;

    -- Table that will handle the records for the splitted columns.
    DROP TABLE IF EXISTS new_sometbl;
    CREATE TABLE new_sometbl(id INT, NAME VARCHAR(250));

    OPEN sourceTable;

      read_loop: LOOP -- Read all the records from sometbl and split columns with pipes.

        FETCH sourceTable INTO id, NAME;

        IF done THEN
            LEAVE read_loop;
        END IF;

        SET pipe = LOCATE(delimeter, NAME);

        IF pipe > 0 THEN -- If the name contains pipe '|' then split and add as a new record.

            SET occur = (SELECT LENGTH(NAME) - LENGTH(REPLACE(NAME, delimeter, '')) + 1 );
            SET i = 1;

            WHILE i <= occur DO

                SET splitted_name = (SELECT REPLACE(SUBSTRING(SUBSTRING_INDEX(NAME, delimeter, i), LENGTH(SUBSTRING_INDEX(NAME, delimeter, i - 1)) + 1), delimeter, ''));
                INSERT INTO new_sometbl(id, NAME) VALUES (id, splitted_name);
                SET i = i + 1;

            END WHILE;
        ELSE -- The name has no piple so add it directly to the new table.
            INSERT INTO new_sometbl VALUES (id, NAME);
        END IF;

      END LOOP;

      SELECT * FROM new_sometbl;

    CLOSE sourceTable;
END;
$$

DELIMITER ;

Share on Google Plus

About Admin

Arun is a JAVA/J2EE developer and passionate about coding and managing technical team.