Split Function in MySQL to Delimited string into Rows

Razztak
Razztak used Ask the Experts™
on
Hello,

I have a table with ID and Keywords, in keywords field words is separate by ';'. iI want split each delimited sting into rows. Some rows have 1, 2, 3 to 6 or 7 word separated by ;.

Source Table:
ID     keywords
-----------------------
38        RedeTV! ; Mônica Pimentel
39       Rádio Show
40       Alexandre Pires
41       Famosos
42       Band
43       Camila Tittinger ; All TV ; Band ; Show

Result Table:
ID     keywords
-----------------------
38        RedeTV!
38    Mônica Pimentel
39       Rádio Show
40       Alexandre Pires
41       Famosos
42       Band
43       Camila Tittinger
43    All TV
43    Band
43    Show

Of course i need the script work in MySQL 5.x

Thanks in Advance,
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
http://forge.mysql.com/tools/tool.php?id=4

above mention link add the one more parameter id and insert the same in temeporaty table ....

this link may help out
theGhost_k8Database Consultant

Commented:
Here is the quick procedure.

I have a "tmp" table with column "cat" with ";" separated values.
Following method will insert all values into other table after splitting "cat" values by ";".

1. create table:
    CREATE TABLE `my_splits` (
           `splitted_column` varchar(45) NOT NULL
         ) ENGINE=MyISAM DEFAULT CHARSET=latin1;
2. create procedure.
3. execute procedure.
4. check table my_splits.

* you may update it with your ID specific requirements, I suppose it'd be easy or may be you can do that using join+find in set query.

Happy Weekend!!
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 ins_query VARCHAR(500);
DECLARE tmp_query VARCHAR(500);
DECLARE splitter_cur CURSOR FOR
SELECT cat from test.tmp;
 
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done=1;
 
OPEN splitter_cur;
splitter_loop:LOOP
      FETCH splitter_cur INTO split_string;
 
SET occurance=length(split_string)-length(replace(split_string,';',''))+1;
SET my_delimiter=';';
  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 test.my_splits(splitted_column) values(", concat("SUBSTRING_INDEX(SUBSTRING_INDEX( '",split_string ,"', '",my_delimiter,"', ",i, "),'",my_delimiter,"',-1));"));
        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 test.my_splits(splitted_column) values(",split_string,"');");
        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

Open in new window

HainKurtSr. System Analyst

Commented:
I am not good at MySQL but I am sure you fix this query if it fails...
select distinct id, trim(kw) as keyword from (
  select id, SUBSTRING_INDEX(keywords, ';', 1) as kw from KWTable
  union
  select id, SUBSTRING_INDEX(SUBSTRING(keywords, ';', 2),';',-1) from KWTable
  union
  select id, SUBSTRING_INDEX(SUBSTRING(keywords, ';', 3),';',-1) from KWTable
  union
  select id, SUBSTRING_INDEX(SUBSTRING(keywords, ';', 4),';',-1) from KWTable
  union
  select id, SUBSTRING_INDEX(SUBSTRING(keywords, ';', 5),';',-1) from KWTable
  union
  select id, SUBSTRING_INDEX(SUBSTRING(keywords, ';', 6),';',-1) from KWTable
  union
  select id, SUBSTRING_INDEX(keywords, ';', -1) from KWTable
) where kw is not null

Open in new window

Ensure you’re charging the right price for your IT

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

HainKurtSr. System Analyst

Commented:
note that above solution is based on assumption that max keyword in keywords is 7...

Author

Commented:
Thank you to both,

theGhost_k8 you soluction is ok, but i need the procedure insert id,  ¿Why?

why some words repeats many times, if i used join+find i dont know exactly id for each word. This for a keywords table asociate to a news table and some news use same word and need asosiate news ID with keyword ID.

Thanks again,
theGhost_k8Database Consultant

Commented:
Hi following query fixes id issues... Change table names accordingly.

mysql> update tmp,my_splits set my_splits.id=tmp.id where find_in_set(splitted_column,replace(cat,';',','));
You may add ID column to your my_splits table and fire following update query.

I'm little lazy to update the SP now, I hope it works for you!

Author

Commented:
if you see keyword "A Casa é Sua" repeat 116 times. all with diferents ID.
id      keywords
538       A Casa é Sua
653       A casa é sua
676       A Casa é Sua
894       Sabrina Sato ; A casa é sua
1314       Gustavo Baena ; A Casa é Sua
1411       A Casa é Sua
1433       A casa é sua
.........

with update you
"query mysql> update tmp,my_splits set my_splits.id=tmp.id where find_in_set(splitted_column,replace(cat,';',','));"

can you see the id is same for all keyword named "A Casa é Sua" when in original tables have 116 diferents id's.

ID      splitted_column
538       A Casa é Sua
538       A Casa é Sua
538       A Casa é Sua
538       A Casa é Sua
538       A Casa é Sua
538       A Casa é Sua
538       A Casa é Sua
538       A Casa é Sua
538       A Casa é Sua
538       A Casa é Sua
538       A Casa é Sua
538       A Casa é Sua
538       A Casa é Sua
538       A Casa é Sua
538       A Casa é Sua
538       A Casa é Sua
538       A Casa é Sua
theGhost_k8Database Consultant

Commented:
Right, I agree for a repeated value even id will repeat... You still can modify the SP; can't you?
theGhost_k8Database Consultant

Commented:
It took minutes.
Check below code now:
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 test.tmp;
 
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done=1;
 
OPEN splitter_cur;
splitter_loop:LOOP
      FETCH splitter_cur INTO split_id,split_string;
 
SET occurance=length(split_string)-length(replace(split_string,';',''))+1;
SET my_delimiter=';';
  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 test.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 test.my_splits(splitted_column) values(",split_string,"');");
        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 ;

Open in new window

theGhost_k8Database Consultant

Commented:
You may comment-out Line - 35:   select ins_query;

Database Consultant
Commented:
with corrections
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 test.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 occurance=length(split_string)-length(replace(split_string,';',''))+1;
SET my_delimiter=';';
  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 ;

Open in new window

Author

Commented:
mmmm..... i see that script dont work correctly, i attach table Keyword_temp with ID and Keywords.

The procedure work same that last soluctioón, make you a test.

Thanks for you time.

Razz
keywords-temp.zip
theGhost_k8Database Consultant

Commented:
I altered keywords_temp.txt for table / column names and used as follows:

1. mysql -uroot -pkedar test < keywords_temp.txt
2. call split_string();
3. select * from my_splits;

Attachments:
- My keywords_temp.txt
- Output exported in excel after executing step 3.

What is missing?
keywords-temp.txt
my-splits.xls

Author

Commented:
well, im back, work trip.

Im my server dont work, i try mount a mysql server and try again.

Thanks for you help.

Author

Commented:
well, im back, work trip.

Im my server dont work, i try mount a mysql server and try again.

Thanks for you help.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial