Is there a way to separate a column into two?

Question

Is there a way to separate a column into two ?

The column its like this

BRAND/PRODUCT

And I wanted to insert them into 2 new columns BRAND and PRODUCT

Thanks

Is there a \ between the values for BRAND and PRODUCT in every row of the table? — Kris Gruttemeyer, Jun 30 '14 at 20:04
take a look at this answer: http://stackoverflow.com/questions/2696884/mysql-split-value-from-one-field-to-two — gipinani, Jun 30 '14 at 20:04

score 8 · Accepted Answer · answered Jun 30 '14 at 20:04

8

Use LOCATE to find the /, and SUBSTR to grab the parts of the string before and after it.

UPDATE table
SET brand = SUBSTR(brand_product, 1, LOCATE('/', brand_product)-1),
    product = SUBSTR(brand_product, LOCATE('/', brand_product)+1)

answered Jun 30 '14 at 20:04

Barmar

bloodyKnuckles · Answer 2 · 2014-06-30T20:20:01.723

Essentially splits the field value. The "1" in the first one gets the first text segment, the "-1" in the second one gets the last text segment.

UPDATE `table` 
  SET `brand`   = SUBSTRING_INDEX(`brand_product`, '/', 1), 
      `product` = SUBSTRING_INDEX(`brand_product`, '/', -1);

Output:

| brand_product | brand | product |
+---------------+-------+---------+
| one/two       | one   | two     |

2 Answers2