Geek asks and answers

Mysql removes a specific word in a comma-separated string

In my table there was a field "tags":

Tags

tag1, tag2, tag3

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

+1
source share
6 answers

For the SET data type, you can 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 |
+-----------+
+3
source

Besides the first answer that I have not tested, and therefore I have no opinion that others will fail in the following cases:

1- tags = "tag1,tag2,tag22,tag3"
2- tags = "tag2,tag1,tag3"

in the first example, the REPLACE function (tags, 'tag2', '') will remove the third comma-separated value, that is, tag22, and in the second example, tag2 will not be replaced by REPLACE (with tags, ', tag2', '')

One possible solution could be:

REPLACE(REPLACE(CONCAT(',', tags, ','), 'tag2', ''), ',,', ',')

, , . .

+3

, ​​ SET?

, , ,

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

where. . .

+2

MySQL:

replace(FIELD_NAME, ‘find this string’, ‘replacement string’);

:

SELECT REPLACE('tag1,tag2,tag3', ',tag2', '');

. : http://dev.mysql.com/doc/refman/5.0/en/string-functions.html#function_replace

0

, , ... SELECT TRIM(BOTH ',' FROM (SELECT REPLACE(REPLACE('tag1,tag2,tag3','tag2',''),',,',',')))

0

:

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

, :   'Tag1', 'tag2', 'tag3'

'tag3'

:

UPDATE sets SET tags = replace(replace(tags, 'tag2', ''), ',,', '')

"tag2", , tagg

tag22, 2, , OP,

0

More articles:


All Articles