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:
And we want output as follows:
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();
You might also like::
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;
This just saved me tons of time. Thanks!!
I really like this post. This really saves lots of time.
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.
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
Thanks guys, this code worked great.
but i have 11,00,000 records to split, i feel its better to do in batches. Please help me to do it
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.