MySQL procedure to split a column into rows using a delimiter

Suppose, there is a table called sometbl and has the following data into it.

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');

Therefore for (2), example rows would look like >> “3, white”, “3, Snow” …

Example

DELIMITER $$
DROP PROCEDURE IF EXISTS `test`.`SplitValueIntoMultipleRows` $$
CREATE
    PROCEDURE `test`.`SplitValueIntoMultipleRows`()
    BEGIN

	DECLARE r_len INTEGER;
        DECLARE r_id INTEGER;
        DECLARE r_name VARCHAR(50);
        DECLARE i INT DEFAULT 0;
	DECLARE splitted_name VARCHAR(50);
        DECLARE occurances INT DEFAULT 0;
        DECLARE done INT DEFAULT 0;
        DECLARE cur CURSOR FOR SELECT sometbl.id,sometbl.name FROM sometbl;
	DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;
        DROP TEMPORARY TABLE IF EXISTS temp ;
        CREATE TEMPORARY TABLE temp(`id` INT(11),`name` VARCHAR(50));
	OPEN cur;
            read_loop: LOOP
		FETCH cur INTO r_id,r_name;
                IF done THEN
			LEAVE read_loop;
                END IF;
                SET occurances = (SELECT LENGTH(r_name) - LENGTH(REPLACE(r_name, '|', ''))+1);
		SET i = 1;
		WHILE i <= occurances DO
		  SET splitted_name = (SELECT REPLACE(SUBSTRING(SUBSTRING_INDEX(r_name, '|', i),
			LENGTH(SUBSTRING_INDEX(r_name, '|', i - 1)) + 1), '|', ''));
		  INSERT INTO temp VALUES (r_id, splitted_name);
		  SET i = i + 1;
		END WHILE;
            END LOOP;
        CLOSE cur;
        SELECT * FROM temp;
        DROP TEMPORARY TABLE temp;
        
    END$$

DELIMITER ;

Usage

CALL SplitValueIntoMultipleRows()

Output

mysql procedure to split column values

Thanks for reading.

1 thought on “MySQL procedure to split a column into rows using a delimiter

  1. Good day,

    I hope you are well. Thanks so much for this. How would you amend the code if you have 2 or more fields that have multiple values and the values corrspond to each other’s position?

Leave a Reply

Your email address will not be published. Required fields are marked *