morandi3 morandi3 - 4 months ago 12
MySQL Question

The best way to remove value from SET field?

Which is the best way to update a mysql SET field, to remove a specific value from the field.

Eg. field categories with values: 1,2,3,4,5?
I want to remove '2' from the list:

UPDATE table
SET categories = REPLACE(categories, ',2,', ',')
WHERE field LIKE '%,2,%';


But what if '2' is the first or the last value from the list?

UPDATE table
SET categories = REPLACE(categories, '2,', '')
WHERE field LIKE '2,%';

UPDATE table
SET categories = REPLACE(categories, ',2', '')
WHERE field LIKE ',2%';


How could I handle all 3 cases with one single query?!

Answer

If the value you need to remove from the set can't be present more than once, you could use this:

UPDATE yourtable
SET
  categories =
    TRIM(BOTH ',' FROM REPLACE(CONCAT(',', categories, ','), ',2,', ','))
WHERE
  FIND_IN_SET('2', categories)

see it working here. If the value can be present more than once, this will remove all occourences of it:

UPDATE yourtable
SET
  categories =
    TRIM(BOTH ',' FROM
      REPLACE(
        REPLACE(CONCAT(',',REPLACE(col, ',', ',,'), ','),',2,', ''), ',,', ',')
    )
WHERE
  FIND_IN_SET('2', categories)