Split Function in MySQL to Delimited string into Rows

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

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

Select allOpen in new window

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

Select allOpen in new window

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

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,

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!

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

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

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 ;

Select allOpen in new window

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

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

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

well, im back, work trip.

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

Thanks for you help.

well, im back, work trip.

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

Thanks for you help.