How to only with SQL select column form table A, split its by separator and insert separated values to other table?

Question

sorry for my english :) I need (only with SQL) select all column values form table A, split its by separator and insert separated values to other table?

For example, i have table like this:

ID, NAME, MAGIC
1, Marty, ACD ACFX U128BH

and i need export "MAGIC" value to separate table like this:

ID, USERID, MAGIC
1, 1, ACD
2, 1, ACFX
3, 1, U128BH

How to do it? I found eg. SQL insert data to other table after split string, but this is MS SQL syntax (?) and im using MySQL. Thanx for reply

I don't believe that MySQL has any built in unpivot functionality (as does SQL Server). Beyond that, it would be non trivial to even generate the row numbers you want. — Tim Biegeleisen, Jul 20 '16 at 07:07
Use an user defined function as shown here http://stackoverflow.com/questions/11835155/mysql-split-comma-separated-string-into-temp-table — Madhivanan, Jul 20 '16 at 07:18
That should solve your question: http://www.marcogoncalves.com/2011/03/mysql-split-column-string-into-rows/ — m c, Jul 20 '16 at 11:27

Charif DZ · Answer 1 · 2016-07-20T08:01:54.387

EDIT THIS ANSWER TO MUCH YOUR WORK

#drop table magic_t; create table magic_t( id int, name varchar(100), magic varchar(100) );

#drop table split_insert;
create table split_insert(
split_value varchar(100)
);
insert into magic_t values(1,'nam1','VAL1 VAL2 VAL3');
insert into magic_t values(1,'nam1','VAL4 VAL5 VAL3');

#drop function strSplit;
DELIMITER ;;
CREATE FUNCTION strSplit(x varchar(255), delim varchar(12), pos int) returns varchar(255)
return replace(substring(substring_index(x, delim, pos), length(substring_index(x, delim, pos - 1)) + 1), delim, '');;
DELIMITER ;


#drop procedure split_table;
DELIMITER ;;
CREATE procedure split_table ()
begin
DECLARE done INT DEFAULT 0;
DECLARE while_condition boolean default true;
DECLARE counter INT DEFAULT 0;
DECLARE magic_value varchar(100);
declare splited_value varchar(100); 
DECLARE colum_list CURSOR FOR   SELECT  magic   FROM    magic_t;
DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done = 1; -- to stop loop when fetch is over 
OPEN colum_list;
        REPEAT   FETCH colum_list INTO magic_value;
         if done =0 then
             set counter =1;
             set while_condition = true;
             while(while_condition) do
                 set splited_value = strSplit(magic_value,' ',counter) ;
                 if splited_value <>'' then              
                   insert into split_insert values(splited_value);
                 end if;
             if splited_value='' then set while_condition=false; end if;     
             set counter =counter+1;
             end while;          

         end if;
        UNTIL done  -- here stop repeat when done =1 
    END REPEAT;
end ;;
DELIMITER ;
call split_table ();

select * from split_insert;

sorry for the erreur when i posted the answer i didn't put all my code like a code i edited tell me if it work for you — Charif DZ, Jul 20 '16 at 08:03
if you need any help with the code tell me also see you later — Charif DZ, Jul 20 '16 at 08:09

How to only with SQL select column form table A, split its by separator and insert separated values to other table?

1 Answers1