user655334 user655334 - 7 months ago 33
SQL Question

Mysql remove the specific word in comma seperated string

My table had 'tags' field like this:

Tags



tag1,tag2,tag3

How to remove ',tag2' including comma using mysql query.

Answer

For a SET data type, you could use something like this -

CREATE TABLE sets(tags SET('tag1','tag2','tag3'));
INSERT INTO sets VALUES
  ('tag1,tag2'),
  ('tag1,tag3,tag2'),
  ('tag2,tag3'),
  ('tag3,tag1');

UPDATE sets SET tags = tags &~ (1 << FIND_IN_SET('tag2', tags) - 1);

SELECT * FROM sets;
+-----------+
| tags      |
+-----------+
| tag1      |
| tag1,tag3 |
| tag3      |
| tag1,tag3 |
+-----------+