Page 1 of 2 12 LastLast
Results 1 to 15 of 16
  1. #1
    Join Date
    Apr 2010
    Posts
    30

    Unanswered: Stored Procedure to Split Delimited Strings

    Hi,
    I am trying to migrate data from a temporary table in mysql database that has data in the following format

    1 9 a,b,c
    2 9 a;b;c
    3 9 a-b-c

    It has 3 columns.The 3rd column data has to be split w.r.t the delimiters and migrated to actual tables.So the end result I would require is

    1 9 a
    1 9 b
    1 9 c
    2 9 a
    2 9 b
    2 9 c
    3 9 a
    3 9 b
    3 9 c

    Each row in the column has to be read and suitably moved to the actual tables

    Kindly help me with a sample code that can help me out of this problem.

    Advance Thanks

  2. #2
    Join Date
    Sep 2009
    Location
    San Sebastian, Spain
    Posts
    880
    Hi,

    this can be done through a stored procedure. I have put together an explanation MySQL – data migration example IT Integrated Business Solutions. The code in question is:

    Code:
    CREATE PROCEDURE appenddata()
    BEGIN
    	DECLARE done INT DEFAULT 0;
    	DECLARE a,b INT;
    	DECLARE lstart, lend INT;
    	DECLARE c VARCHAR(20);
    	DECLARE cur CURSOR FOR SELECT f1,f2,f3 FROM tmp_info;
    	DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;
    
    	OPEN cur;
    	read_loop: LOOP
    
    		FETCH cur INTO a, b, c;
    		IF done THEN
    			LEAVE read_loop;
    		END IF;
    		-- Keep all using the same format
    		SET c = REPLACE(c, ";", ",");
    		SET c = REPLACE(c, "-", ",");
    		SET lstart = 1;
    		SET lend = LOCATE(",", c, lstart);
    		WHILE lend > 0 DO
    			INSERT INTO t_info(f1, f2, f3) VALUES (a, b, SUBSTRING(c, lstart, lend-lstart));
    			SET lstart = lend+1;
    			SET lend = LOCATE(",", c, lstart);
    		END WHILE;
    		IF c <> "" THEN
    			INSERT INTO t_info(f1, f2, f3) VALUES (a, b, SUBSTRING(c, lstart));
    		END IF;
    		
    	END LOOP;
    	CLOSE cur;
    END;
    Ronan Cashell
    Certified Oracle DBA/Certified MySQL Expert (DBA & Cluster DBA)
    http://www.it-iss.com
    Follow me on Twitter

  3. #3
    Join Date
    Apr 2010
    Posts
    30
    Thanks Ronan ,your website has given me a better insight to the solution

  4. #4
    Join Date
    Apr 2010
    Posts
    30
    Hi Ronan,
    What if we have data as
    1 2 a,b,d
    2 2 f-j-l
    3 4 y;t;a

    If data is of different values and if there is a large data file to be migrated?

  5. #5
    Join Date
    Sep 2009
    Location
    San Sebastian, Spain
    Posts
    880
    The code that I provide does not look at the values but simply looks at the separator codes. Why don't you create your own test environment and try out the code to see if this works correctly?

    As to the amount of data, how much data, how many data records are we talking about? How much time is available to perform the migration? During the migration will anything else by happening on the database?
    Ronan Cashell
    Certified Oracle DBA/Certified MySQL Expert (DBA & Cluster DBA)
    http://www.it-iss.com
    Follow me on Twitter

  6. #6
    Join Date
    Apr 2010
    Posts
    30
    The data that would be migrated will be of around 5000-10,000 rows and the column 3 data may vary in size.Can the code be used.I am working on creating a test environment.Thanks

  7. #7
    Join Date
    Sep 2009
    Location
    San Sebastian, Spain
    Posts
    880
    This is not a huge number of records. I think my code should work for entries even larger than just a,b,c for example one,two,three this will also work. Keep us posted on your tests and I can modify the code if this becomes a problem.
    Ronan Cashell
    Certified Oracle DBA/Certified MySQL Expert (DBA & Cluster DBA)
    http://www.it-iss.com
    Follow me on Twitter

  8. #8
    Join Date
    Apr 2010
    Posts
    30
    I could run the code on a test environment.I am using SQLYOG Community as the GUI.Had to do small syntax changes aned comment the following to get the code working

    /* DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1; */

    Seems procedure works fine after calling it,but the migration is not happening

    It says:

    Query : Call Sp_test
    Error Code : 1329
    No data - zero rows fetched, selected, or processed
    Execution Time : 00:00:00:000
    Transfer Time : 00:00:00:000
    Total Time : 00:00:00:000

    Not sure why rows are not getting fetched.Kindly let know if you need more details

    Thanks

  9. #9
    Join Date
    Sep 2009
    Location
    San Sebastian, Spain
    Posts
    880
    Not sure why you needed to comment out that line of code. This is used to make sure we break out of the loop correctly.

    There are other exception handlers that you may need to setup i.e. one for no data. For instance, of the tmp table does not have any records if may raise an exception for this. Are you sure that the staging table is correctly setup. Perhaps use the script on the complete solution on my website and start with that.
    Ronan Cashell
    Certified Oracle DBA/Certified MySQL Expert (DBA & Cluster DBA)
    http://www.it-iss.com
    Follow me on Twitter

  10. #10
    Join Date
    Apr 2010
    Posts
    30
    Thanks will let u know

  11. #11
    Join Date
    Apr 2010
    Posts
    30
    Your code works fine.Thanks a ton, your guidance has been great.

  12. #12
    Join Date
    Apr 2010
    Posts
    30
    Ronan,
    I find comma in between data Ega,b),abc,fgr;xys

    For data like this when file is imported into the dB it goes in as

    (a
    b)
    abc
    fgr
    xys

    Actually (a,b) is a single word.How do I resolve this?

    Thanks

  13. #13
    Join Date
    Sep 2009
    Location
    San Sebastian, Spain
    Posts
    880
    You will need to change the logic in the stored procedure to handle this particular scenario. This makes it a little more complex because you will need to identify the starting and ending brackets for a single element.

    Has the data been verified that it will always have a beginning and closing brackets? What about quotes? Will the same logic need to apply for this too? Is there a way of changing the data separator to something like #?
    Ronan Cashell
    Certified Oracle DBA/Certified MySQL Expert (DBA & Cluster DBA)
    http://www.it-iss.com
    Follow me on Twitter

  14. #14
    Join Date
    Apr 2010
    Posts
    30
    Hi,
    Some records have the opening and closing brackets like (a,b) which is a single record but it is getting split after , due to the logic used.Apart from this I do not think any other records have problem being migrated.

    Thanks

  15. #15
    Join Date
    Sep 2009
    Location
    San Sebastian, Spain
    Posts
    880
    In that case you will need to modify the logic of the stored procedure in particular the section dealing with REPLACE to make sure you have commas only in the location where they are supposed to be. This implies the last part of the stored procedure will be unaffected and continue to work as it should.
    Ronan Cashell
    Certified Oracle DBA/Certified MySQL Expert (DBA & Cluster DBA)
    http://www.it-iss.com
    Follow me on Twitter

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •