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

    2. CREATE DEFINER=`vidya`@`%` PROCEDURE `createQueenslandWards`(IN `clsid` INT, IN `secid` INT, IN `peds` INT, IN `test` VARCHAR(200))
      LANGUAGE SQL
      NOT DETERMINISTIC
      CONTAINS SQL
      SQL SECURITY DEFINER
      COMMENT ”
      BEGIN
      DECLARE done INT DEFAULT 0;
      DECLARE a,b,c VARCHAR(255);
      DECLARE m int default 0;
      DECLARE d varchar(100);
      — Declare test varchar(255);
      declare i1 INT DEFAULT 1;
      — DECLARE SLICE nvarchar(4000);
      — DECLARE INDX INT;
      — declare DelimiterChar VARCHAR(1);

      DEClARE section_cursors CURSOR FOR SELECT classId,sectionId FROM section where classId=clsid and sectionId=secid;
      DECLARE timetable_cursors CURSOR FOR SELECT timetableId FROM timetable where timetableId=1;
      DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;
      — set @test=’tuesday’;

      OPEN section_cursors;

      REPEAT
      FETCH section_cursors INTO a,b;
      IF NOT done THEN
      OPEN timetable_cursors;
      block2: BEGIN
      DECLARE doneLat INT DEFAULT 0;
      DECLARE e varchar(150) DEFAULT ”;
      — DECLARE firstLang VARCHAR(255) DEFAULT ”;
      — DECLARE firstLat VARCHAR(255) DEFAULT ”;
      DECLARE i INT;
      DECLARE CONTINUE HANDLER FOR NOT FOUND SET doneLat = 1;

      REPEAT
      FETCH timetable_cursors INTO c;
      set m=LENGTH(SUBSTRING_INDEX(test,”,”,0));
      — dbms_output.put_line(‘no customers updated’ || test);
      SET test = LENGTH(m);
      MyLoop:while(test!=0) do
      — SET e = MID(,);
      — SET m = LENGTH(SUBSTRING_INDEX(test,’,’,1))+1;
      — SET test = MID(test,m,d);

      loopsss1: while i1<=test do

      insert into sepes(classId,sectionId,Periods,weekday) values(a,b,peds,test);

      set i=1;
      loopsss: while i<=peds do
      insert into period(TimeTableId,SequenceNo,Days,PeriodTitle,StartTime,EndTime,Description)values(1,i,m,'first','00:00:00','00:00:00','hi');
      SET i = i + 1;
      END while loopsss;
      set i1=i1+1;
      END while loopsss1;
      SET test = RIGHT(test,LENGTH(test) – test);
      end WHILE MyLoop;
      UNTIL doneLat END REPEAT;
      END block2;
      CLOSE timetable_cursors;
      END IF;
      UNTIL done END REPEAT;

      CLOSE section_cursors;

      END

      and my input is CALL `createQueenslandWards`('1', '51', '3,4,6,7', 'mon,tue,wed,thu') this is my input how to solve it
      one loop inside other loop

      Reply

    3. Hi Guys,
      Can anyone explain me the code as I need to move | separated string to multiple columns of another table.

      Regards,
      Abhishek

      Reply

    4. 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`()

      Reply

    5. 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 !

      Reply

      1. Sneha,

        I think you can do that using LIMIT for splitter_cur cursor.

        Eg. process 10000 records as:

        DECLARE splitter_cur CURSOR FOR
        SELECT id,cat from tmp limit 10000;

        Hope this helps,
        Kedar.

        Reply

    7. Thanks a lot for the procedure, extremely useful. I have a little problem and I would like to ask you for help: when I run it on one of my table I get this error ” Error Code: 1064
      You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ‘NULL’ at line 1″
      And I am really not able to figure out what could it be due to. I get some result in the table “my_splits” (removed the tempoary). I also tried to set both `splitted_column` and ‘id’ to “default NULL”, but I continue to get that error. Any idea? Thank you very much.

      Reply

      1. Hey MIchael,

        I’d like to know your exact scenario!
        Meanwhile make sure you’re not copying procedure from page but downloading it from the link.
        Once procedure get loaded into db, you may uncomment some of the lines to debug through!

        Cheers,
        Kedar

        Reply

        1. Hi Micheal and Kedar,

          Thank you so much Kedar for this amazing script, this really helps me a lot.

          I encountered Micheal’s same error and in my scenario, the error is caused by cells that have “‘” (apostrophe or single quotation mark) in the splitting column, my fix is to replace the “‘” in the column with a space ” ” and then it works smoothly. Base on the same reason I would believe if the column contents ‘”‘ (quotation mark) it will mess up things as well, so better replace both of them with a space.

          Hope this can help someone later.

          Regards,
          ALex

          Reply

    10. Hi Thanks it works.

      Now, I had to use _space_ as delimiter, something that did not work very well with the above.

      But doing a replace ” ” -> “;” on the select fixes that.

      SELECT id,REPLACE(cat, ‘ ‘, ‘;’) from tmp;

      Reply
    Leave a Reply

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