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

    Related Tags
    32 comments

    1. happy to help 🙂
      actually i reached this solution out by luck :s
      i tried removing lines one by one untill it worked… then i realized that execute command is the reason of the problem..

      but seems like u have a proof of that and have more information about it.. u can tell me any info if u dont mind.. i’m starting with stored procedures these days..

      thanks in advance
      and many thanks to the author Kedar :):)

      Reply

    2. i figured out that EXECUTE command is the reason of my problem.. i removed it and worked around it.. it inserts successfully now..
      i replaced this part :
      //
      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,”);”));
      set @ins_query=ins_query;
      PREPARE ins_query from @ins_query;
      EXECUTE ins_query;
      //

      with this part
      //
      SET ins_query= SUBSTRING_INDEX(SUBSTRING_INDEX( split_string, my_delimiter, i),my_delimiter,-1);
      set @ins_query=ins_query;
      insert into my_splits values(@ins_query,split_id);
      //

      thanks 🙂

      Reply

    3. If you google this you’ll find many of the times this has to do with older versions… Please check versions…

      Also, Do make sure you make proper changes to SP as per your requirements.

      May be you can share sample data on db4free.net to get it clear!

      Reply

    4. Kadar,
      thanks for your reply.
      actually i had tried a very simple statement like that insert statement and it worked fine.
      but when i try the whole stored it gives me the mentioned error.
      i tried to figure out which part of it that causes that error but i couldn’t cause it’s my first time with stored procedures with mysql and phpmyadmin.

      ========================================
      ||’Let me know how you’re using this.’||
      ========================================

      i created the stored in phpmyadmin and then called it in phpmyadmin too. i’m trying to use the stored to split a string in one row in x table into multiple rows in y table.

      Reply

    5. Heba,
      I’m not sure how you’re using this, but this is very shallow example and not at-all generalized. You might need to update it as per your requirements.

      Try below table definition:

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

      And run procedure from prompt or other way.

      Let me know how you’re using this.

      Reply

    6. it’s a very useful piece of code .. thank u.. but atually i’m unable to call the procedure coz i get this error : #1312 – PROCEDURE can’t return a result set in the given context .. do u have any idea about the solution..

      Reply

    8. thank you very much for the tip. I’m using this technique on a project I will soon release as GPL, an opensource subscription manager for small magazines. your procedure is very useful in my project to build the delivery packing list, piking up the list of magazine numbers subscribed from a comma-separated list. I will credit your work as soon as it is ready. cheers! pierinux

      Reply

    10. I would love to see the screenshots that you’re linking to in this post but the links are dead. Can you relink for me?

      kedar.nitty-witty.com/blog/wp-content/uploads/2009/12/source-table.JPG

      kedar.nitty-witty.com/blog/wp-content/uploads/2009/12/output-table.jpg

      Thanks!

      Reply
    Leave a Reply

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