Split row field mysql create new columns

Question

I have a MySQL table containing several fields; like this:

id  name  ton           dyr
1   moo   FRET_GTR_HYU  ra
2   maa   DER_GY_TR_DB  fe
3   tee   SAME_IQ       tete
...

The question is that the contents for every row of the column ton are independent, but they are joined with a symbol "_".

I would like to modify the table, splitting the field ton to end up keeping all the same entries in the other fields, but just one entry per row in the ton field, like this...

id  name  ton      dyr
1   moo   FRET     ra
1   moo   GTR      ra
1   moo   HYU      ra
2   maa   DER      fe
2   maa   GY       fe
2   maa   TR       fe
2   maa   DB       fe
3   tee   SAME     tete
3   tee   IQ       tete

...

I tried to modify the code given in this page to perform the job, but as i'm not that expert in MySQL, i am not able to get it to work my way. In the link above, the code is given suitable to rewrite the two columns given, not any other fields.

Any help on how to modify the code, or do it another way?

Jérôme · Accepted Answer · 2012-07-03 00:50:08Z

1) Copy table1 structure to table2

2) Add the 2 stored procedures to your database.

CREATE PROCEDURE peixe()
BEGIN
  DECLARE done BOOLEAN DEFAULT 0;
  DECLARE i INT;
  DECLARE cursor_id CURSOR FOR SELECT id FROM table1 WHERE ton LIKE '%_%';
  DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done=1;
  OPEN cursor_id;
  FETCH cursor_id INTO i;
  REPEAT
    CALL split_ton(i);
    FETCH cursor_id INTO i;
  UNTIL done END REPEAT;
  CLOSE cursor_id;
END

CREATE PROCEDURE split_ton(i INT)
BEGIN
  DECLARE str_name VARCHAR(50);
  DECLARE str_ton VARCHAR(50);
  DECLARE str_ton_el VARCHAR(50);
  DECLARE str_dyr VARCHAR(50);
  DECLARE ton_size INT;

  SELECT name, ton, dyr
  INTO str_name, str_ton, str_dyr
  FROM table1 WHERE id=i;

  SET ton_size = LENGTH(str_ton) - LENGTH(REPLACE(str_ton, '_', '')) + 1;

  WHILE ton_size > 0 DO
    SET str_ton_el = SUBSTRING_INDEX(str_ton, '_', -1);
    SET str_ton = SUBSTRING_INDEX(str_ton, '_', ton_size - 1);
    INSERT INTO table2 (id, name, ton, dyr) VALUES (i, str_name, str_ton_el, str_dyr);
    SET ton_size = ton_size - 1;
  END WHILE;
END

3) Finally, execute the following statement :

CALL peixe

And I think, you're done :-)

Jérôme · Accepted Answer · 2012-07-02 15:37:48Z

1

You should use the SUBSTRING_INDEX MySQL string function. The following should return what you are asking :

INSERT INTO table2
SELECT id, name, SUBSTRING_INDEX(ton, '_', 1), dyr FROM table1
UNION
SELECT id, name, SUBSTRING_INDEX(ton, '_', -1), dyr FROM table1

edited Jul 2, 2012 at 15:37

answered Jul 2, 2012 at 15:31

Jérôme

2,09015 silver badges21 bronze badges

4 Comments

peixe Over a year ago

Wow! This is exactly the command i was looking for... It works nice, but i realized in certain rows i got more than 2 records, so it wont split them well. I will edit the question

Jérôme Over a year ago

Now you need something much more clever. For instance, a recursive function executed (n + 1) times with n = number of occurrences of '_' in ton.

Jérôme Over a year ago

@peixe have you had the chance to test my other proposition for 'ton' containing more than one underscore?

peixe Over a year ago

I hadn't time yet, because in the end i did it in other way, a bit "dirty" BTW... :P But be sure that as soon as i had some spare time, i will test it. The proposition looks pretty good. I´ll keep u updated with it

Collectives™ on Stack Overflow

Split row field mysql create new columns

2 Answers 2

Comments

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related