{"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":{"0":"post-290","1":"post","2":"type-post","3":"status-publish","4":"format-standard","6":"category-mysql","7":"category-mysql-scripts-mysql","8":"tag-code","9":"tag-mysql","10":"tag-split-delimited-string","11":"tag-split-string","12":"tag-stored-procedure","13":"tag-string-to-rows"},"aioseo_notices":[],"amp_enabled":true,"_links":{"self":[{"href":"https:\/\/kedar.nitty-witty.com\/blog\/wp-json\/wp\/v2\/posts\/290","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/kedar.nitty-witty.com\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/kedar.nitty-witty.com\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/kedar.nitty-witty.com\/blog\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/kedar.nitty-witty.com\/blog\/wp-json\/wp\/v2\/comments?post=290"}],"version-history":[{"count":5,"href":"https:\/\/kedar.nitty-witty.com\/blog\/wp-json\/wp\/v2\/posts\/290\/revisions"}],"predecessor-version":[{"id":2827,"href":"https:\/\/kedar.nitty-witty.com\/blog\/wp-json\/wp\/v2\/posts\/290\/revisions\/2827"}],"wp:attachment":[{"href":"https:\/\/kedar.nitty-witty.com\/blog\/wp-json\/wp\/v2\/media?parent=290"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/kedar.nitty-witty.com\/blog\/wp-json\/wp\/v2\/categories?post=290"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/kedar.nitty-witty.com\/blog\/wp-json\/wp\/v2\/tags?post=290"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}