The best way to remove value from SET field?

Question

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 1

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)

Answer 2

update TABLE
set COLUMN = COLUMN & ~NUM
where COLUMN & NUM

Taken from comments section of: http://dev.mysql.com/doc/refman/5.0/en/set.html

Beware though, NUM is not the value '2' but its internal index. So if you defined the field like "set ('1,'2','3','4','5')" then the corresponding indexes of these values are (1,2,4,8,16).

Answer 3

The best way is not to save values separated by a comma on the table.

But to answer your question, you can use CASE on this,

UPDATE table 
SET categories = CASE WHEN field LIKE '%,2,%'  -- In the middle
                           THEN REPLACE(categories, ',2,', ',')
                      WHEN field LIKE '2,%'    -- At the beginning
                           THEN REPLACE(categories, '2,', '')
                      WHEN field LIKE '%,2'    -- At the end
                           THEN REPLACE(categories, ',2', '') 
                      WHEN field = '2'         -- At whole
                           THEN '' 
                 END
WHERE FIND_IN_SET('2', categories)