05 Dec

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.

[ad#ad-2-300x250]

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

20 thoughts on “MySQL Stored procedure – Split Delimited string into Rows

  1. Hi There,

    I need a little help handling TLV’s.
    I have a TLV which contains around 20 tags and i need to split that tlv in groups of 9.
    So for example if the TLV has 20 tags then it should got broken into 9 + 9 + 2 tags.
    Using Nested loops will work but i am finding hard to implement it in PL SQL.
    have anyone encountered the same problem or have some relevent procedure, Do mail me @ amazing.ankit@gmail.com

    Thanks !

  2. Thanks a bunch! With some minor tweaking I got it working for my application. Here are some things I found out:

    To change the character used for delimiting you need to change 2 areas, I changed the characters to ! exclamations to make it easier to see:
    line 30: SET occurance=length(split_string)-length(replace(split_string,'!',''))+1;
    line 31: SET my_delimiter='!';

    To run the procedure:
    CALL split_string();

    This will put the data in a temporary table named “my_splits” with fields ‘id’ and ‘splitted_column’, make sure you use that data before the temporary table is gone.

    Here is the sample “tmp” table (thank previous poster):

    CREATE TABLE `tmp` (
    `id` int(11) DEFAULT NULL,
    `cat` varchar(10) DEFAULT NULL
    ) ENGINE=MyISAM DEFAULT CHARSET=latin1;

    insert into tmp values(1,'a;b;c'),(2,'d;e;f'),(3,'y;z');

    You may need to change line 4 for your webserver config
    CREATE DEFINER=`root`@`localhost` PROCEDURE `split_string`()

Leave a Reply

-- Kedar Vaijanapurkar --