MySQL Stored procedure – Split Delimited string into Rows

This procedure will split  a “;” separated column in to new fields preserving ids.

This is very specific problem, lets check it with example.

Consider a sample table test:

source-table


And we want output as follows:

output-table


So again I wrote a procedure.

Procedure will read a data from “tmp” table and will split data by my_delimiter delimiter and generate a temporary table.

You may alter it as per requirement.

Download Stored Procedure : split_string

DELIMITER $$

DROP PROCEDURE IF EXISTS `split_string` $$
CREATE DEFINER=`root`@`localhost` PROCEDURE `split_string`()
BEGIN

DECLARE my_delimiter CHAR(1);
DECLARE split_string varchar(255);
DECLARE done INT;
DECLARE occurance INT;
DECLARE i INT;
DECLARE split_id INT;
DECLARE ins_query VARCHAR(500);
DECLARE splitter_cur CURSOR FOR
SELECT id,cat from tmp;

DECLARE CONTINUE HANDLER FOR NOT FOUND SET done=1;

DROP TEMPORARY TABLE IF EXISTS `my_splits`;
CREATE TEMPORARY TABLE `my_splits` (
`splitted_column` varchar(45) NOT NULL,
`id` int(10) default NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

OPEN splitter_cur;
splitter_loop:LOOP
FETCH splitter_cur INTO split_id,split_string;

SET my_delimiter=';';
SET occurance=length(split_string)-length(replace(split_string,my_delimiter,''))+1;
IF done=1 THEN
LEAVE splitter_loop;
END IF;
#  select occurance;
IF occurance > 0 then
#select occurance;
set i=1;
while i <= occurance do
#        select concat("SUBSTRING_INDEX(SUBSTRING_INDEX( '",split_string ,"', '",my_delimiter,"', ",i, "),'",my_delimiter,"',-1);");
SET ins_query=concat("insert into my_splits(splitted_column,id) values(", concat("SUBSTRING_INDEX(SUBSTRING_INDEX( '",split_string ,"', '",my_delimiter,"', ",i, "),'",my_delimiter,"',-1),",split_id,");"));
#    select ins_query;
set @ins_query=ins_query;
PREPARE ins_query from @ins_query;
EXECUTE ins_query;
set i=i+1;
end while;
ELSE
set ins_query=concat("insert into my_splits(splitted_column,id) values(",split_string,"',",split_id,");");
set @ins_query=ins_query;
PREPARE ins_query from @ins_query;
EXECUTE ins_query;
END IF;
set occurance=0;
END LOOP;

CLOSE splitter_cur;
END $$
DELIMITER ;

Usage:

call split_string();
32 comments
  1. HI

    I have ran your code in my environment but when i try to call the sp it showing #1305 – PROCEDURE split_string does not exist.

    Can you please help me on this

  2. Since you are dealing with strings, I would use CHAR_LENGTH() instead of LENGTH() to also serve multi-byte characters. Otherwise your procedure can mess up the string lengths of various languages with special characters.

Leave a Reply

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

You May Also Like

Stored procedure to Find database objects

This procedure lists available database objects under passed database name. It lists present Tables, Views, Stored Procedures, Functions and Triggers under particular database. It also lists storage engine of tables.…
View Post