2

My table had 'tags' field like this:

Tags

tag1,tag2,tag3

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

user655334
  • 965
  • 3
  • 15
  • 27
  • 3
    If you are storing comma-delimited strings in a database field, your database is not normalized, a possible severe design flaw. [Please read up on database normalization](http://en.wikipedia.org/wiki/Database_normalization). – Charles Mar 28 '11 at 04:20

6 Answers6

4

I think the best answer to such issue is on the below link The best way to remove value from SET field?

query should be like this which covers the ,value or value, or only value in the comma separated column 

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

Here you can have your condition in where clause. for more details refer above link.

Jirka Picek
  • 589
  • 5
  • 19
PravinDodia
  • 3,271
  • 1
  • 18
  • 22
3

Apart from the first answer which I didn't test and therefore I have no opinion about, the others would fail in the below cases:

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

in the first example, the REPLACE(tags, 'tag2', '') function will remove the third comma separted value i.e. tag22 as well, and in the second example tag2 will not be replaced by REPLACE(tags, ',tag2', '')

one possible solution can be:

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

the problem with my solution is that you will always ending up with having values stores with redundant commas both at the start and the end of string. but in my case it works well.

Mohammad Ali
  • 31
  • 1
3

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 |
+-----------+
Devart
  • 119,203
  • 23
  • 166
  • 186
  • Please note that this works only if your field is actually type SET (possibly ENUM too, but didn't test). If you have just varchar for with some comma separated values, you should use answer from PravinDodia below. – Devtrix.net Nov 30 '17 at 09:38
0

A quick tip:

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

Does not work when your set looks like: 'tag1','tag2','tag3'

'tag3' will get lost by this operation

I ended up using a simple double replace:

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

The wrapping replace removes left over comma's from replacing just 'tag2', as you can't be sure if your tagg will be the first or last item of the set

regarding the case of tag22 leaving a 2, doing it without leading zero's as the OP did, your bound for trouble anyway

nachtwerk
  • 1
0

Use replace function in MySQL:

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

so in your case:

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

For more details look at: http://dev.mysql.com/doc/refman/5.0/en/string-functions.html#function_replace

anubhava
  • 761,203
  • 64
  • 569
  • 643
  • Thanks a lot. How do i check the comma in a given string using php regular expressions. – user655334 Mar 28 '11 at 04:29
  • You should probably add that to the original question, or pose a new question, but check out preg_match: http://php.net/manual/en/function.preg-match.php – squawknull Mar 28 '11 at 04:31
  • Thanks. I found a query for getting only comma seperated results using : select * from library where user_id=12 and user_tags REGEXP '\\,' – user655334 Mar 28 '11 at 04:43
  • @user655334: If you are just looking for presence of comma `,` in a column then no need to use REGEXP, just use: `select * from library where user_id=12 and user_tags like '%,%'` which will be faster that regex. – anubhava Mar 28 '11 at 11:53
0

I found a solution, but would love to know if there is an easier way of doing it... SELECT TRIM(BOTH ',' FROM (SELECT REPLACE(REPLACE('tag1,tag2,tag3','tag2',''),',,',',')))

ironic_ollins
  • 156
  • 2
  • 6
  • 1
    This does not work for 'tag1,tag2,tag3,...,tag22,...' => a "2" will remain when replacing tag22. – djot Nov 13 '12 at 17:24