DROP PROCEDURE IF EXISTS splitcolumntorow;

DELIMITER ||

CREATE PROCEDURE splitcolumntorow(IN rownumber INT)

BEGIN

DECLARE found INT DEFAULT 0;

DECLARE value VARCHAR(50);

DECLARE finished INTEGER DEFAULT 0;

DECLARE count INT DEFAULT 1;

DECLARE cursor_names

CURSOR FOR SELECT NAME FROM sometbl WHERE id = rownumber;

DECLARE CONTINUE HANDLER FOR NOT FOUND SET finished = 1;

DROP TEMPORARY TABLE IF EXISTS tempTable;

CREATE TEMPORARY TABLE tempTable (ID INT, NAME VARCHAR(50));

OPEN cursor_names;

process_names: LOOP

FETCH cursor_names INTO value;

if finished = 1 THEN

LEAVE process_names;

END IF;

WHILE count = 1 DO

BEGIN

SET found = INSTR(value, '|');

IF found = 0 THEN

BEGIN

INSERT INTO tempTable VALUES (rownumber, value);

SET count = 0;

END;

ELSE

BEGIN

INSERT INTO tempTable VALUES (rownumber, SUBSTRING(value, 1, found - 1));

SET value = SUBSTRING(value, found + 1);

END;

END IF;

END;

END WHILE;

END LOOP;

SELECT * FROM tempTable WHERE id = rownumber;

DROP TEMPORARY TABLE IF EXISTS tempTable;

END ||

DELIMITER ;

CALL getNames(3);