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

Question

Here is my table:

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

Table output

----------------------------------------
ID NAME
1 Smith
2 Julio|Jones|Falcons
3 White|Snow
4 Paint|It|Red
5 Green|Lantern
6 Brown|bag
------------------------------------------

The output should look like below:

For (2), example rows would look like >> “3, white”, “3, Snow” ...

please help me.

score 0 · Answer 1 · answered Feb 09 '17 at 05:54

-- sample data = 2 One|Two|Three|Four

-- get the first bit: 2 One
SELECT ID, substring_index(NAME, '|', 1) AS NAME FROM tab

UNION

-- get the 2nd bit: 2 Two
SELECT ID, substring_index(substring_index(NAME, '|', 2),'|',-1) AS NAME FROM tab

UNION

-- get the 3rd bit: 2 Three
SELECT ID, substring_index(substring_index(NAME, '|', 3),'|',-1) AS NAME FROM tab

UNION

-- get the last bit: 2 Four
SELECT ID, substring_index(NAME, '|',-1) AS NAME FROM tab

I have need procedure in MySQL,not in query – Bhadresh Shiroya Feb 09 '17 at 06:04 — Bhadresh Shiroya, Feb 09 '17 at 06:04

score 0 · Answer 2 · edited Jun 30 '17 at 13:07

Try this will help you.

DROP PROCEDURE IF EXISTS splitted_table;

DELIMITER $$

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;

    DROP TABLE IF EXISTS new_sometbl;
    CREATE TABLE new_sometbl(id INT, name VARCHAR(250));

    OPEN sourceTable;      
        read_loop: LOOP         
            FETCH sourceTable INTO id, name;                
            SET pipe = LOCATE(delimeter, name);        
            IF pipe > 0             
                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 
                INSERT INTO new_sometbl VALUES (id, name);
            END IF;                
        END LOOP;

      SELECT * FROM new_sometbl;

    CLOSE sourceTable;
END$$
DELIMITER ;

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

2 Answers2