{"id":290,"date":"2009-12-05T19:58:35","date_gmt":"2009-12-05T19:58:35","guid":{"rendered":"http:\/\/kedar.nitty-witty.com\/?p=290"},"modified":"2023-04-26T07:43:13","modified_gmt":"2023-04-26T07:43:13","slug":"mysql-stored-procedure-split-delimited-string-into-rows","status":"publish","type":"post","link":"https:\/\/kedar.nitty-witty.com\/blog\/mysql-stored-procedure-split-delimited-string-into-rows","title":{"rendered":"MySQL Stored procedure \u2013 Split Delimited string into Rows"},"content":{"rendered":"\n

This procedure will split  a \u201c;\u201d separated column in to new fields preserving ids.<\/p>\n\n\n\n

This is very specific problem, lets check it with example.<\/p>\n\n\n\n

Consider a sample table test:<\/p>\n\n\n

\n
\"source-table\"<\/a><\/figure>\n<\/div>\n\n\n


<\/span><\/p>\n\n\n\n

And we want output as follows:<\/p>\n\n\n

\n
\"output-table\"<\/a><\/figure>\n<\/div>\n\n\n


<\/span><\/span><\/p>\n\n\n\n

So again I wrote a procedure.<\/p>\n\n\n\n

Procedure will read a data from \u201ctmp\u201d table and will split data by my_delimiter delimiter and generate a temporary table.<\/p>\n\n\n\n

You may alter it as per requirement.<\/p>\n\n\n\n

Download Stored Procedure : <\/strong>split_string<\/strong><\/a>

DELIMITER $$\n\nDROP PROCEDURE IF EXISTS `split_string` $$\nCREATE DEFINER=`root`@`localhost` PROCEDURE `split_string`()\nBEGIN\n\nDECLARE my_delimiter CHAR(1);\nDECLARE split_string varchar(255);\nDECLARE done INT;\nDECLARE occurance INT;\nDECLARE i INT;\nDECLARE split_id INT;\nDECLARE ins_query VARCHAR(500);\nDECLARE splitter_cur CURSOR FOR\nSELECT id,cat from tmp;\n\nDECLARE CONTINUE HANDLER FOR NOT FOUND SET done=1;\n\nDROP TEMPORARY TABLE IF EXISTS `my_splits`;\nCREATE TEMPORARY TABLE `my_splits` (\n`splitted_column` varchar(45) NOT NULL,\n`id` int(10) default NULL\n) ENGINE=MyISAM DEFAULT CHARSET=latin1;\n\nOPEN splitter_cur;\nsplitter_loop:LOOP\nFETCH splitter_cur INTO split_id,split_string;\n\nSET my_delimiter=';';\nSET occurance=length(split_string)-length(replace(split_string,my_delimiter,''))+1;\nIF done=1 THEN\nLEAVE splitter_loop;\nEND IF;\n#  select occurance;\nIF occurance > 0 then\n#select occurance;\nset i=1;\nwhile i <= occurance do\n#        select concat(\"SUBSTRING_INDEX(SUBSTRING_INDEX( '\",split_string ,\"', '\",my_delimiter,\"', \",i, \"),'\",my_delimiter,\"',-1);\");\nSET 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,\");\"));\n#    select ins_query;\nset @ins_query=ins_query;\nPREPARE ins_query from @ins_query;\nEXECUTE ins_query;\nset i=i+1;\nend while;\nELSE\nset ins_query=concat(\"insert into my_splits(splitted_column,id) values(\",split_string,\"',\",split_id,\");\");\nset @ins_query=ins_query;\nPREPARE ins_query from @ins_query;\nEXECUTE ins_query;\nEND IF;\nset occurance=0;\nEND LOOP;\n\nCLOSE splitter_cur;\nEND $$\nDELIMITER ;<\/code><\/pre>\n\n\n\n
Usage: <\/p>\n\n\n\n
call split_string();<\/code><\/pre>\n","protected":false},"excerpt":{"rendered":"This procedure will split  a \u201c;\u201d separated column in to new fields preserving ids. This is very specific problem, lets check it with example. Consider a sample table test: And…\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"om_disable_all_campaigns":false,"_monsterinsights_skip_tracking":false,"_monsterinsights_sitenote_active":false,"_monsterinsights_sitenote_note":"","_monsterinsights_sitenote_category":0,"footnotes":""},"categories":[8,378],"tags":[24,427,249,250,108,251],"class_list":["post-290","post","type-post","status-publish","format-standard","category-mysql","category-mysql-scripts-mysql","tag-code","tag-mysql","tag-split-delimited-string","tag-split-string","tag-stored-procedure","tag-string-to-rows"],"aioseo_notices":[],"aioseo_head":"\n\t\t\n\t\n\t\n\t\n\t\n\t\n\t\n\t\n\t\n\t\t\n\t\t\n\t\t\n\t\t\n\t\t\n\t\t\n\t\t\n\t\t\n\t\t\n\t\t\n\t\t\n\t\t