What's the command to achieve this:
MYSQL: delete all rows containing string "foo" in table "bar"
Add a comment
|
8 Answers
DELETE FROM bar where
field1 like '%foo%'
OR
field2 like '%foo%'
OR
...
fieldLast like '%foo%'
4 Comments
Toni Michel Caubet
wouldn't this remove rows with fooo or fooh value?
Byron Whitlock
@ToniMichelCaubet yes, fooo or foooh are string that contain foo.
Toni Michel Caubet
True! It's just that I ended up here looking for a different need ;) Sorry about that
Byron Whitlock
you could try like '% foo' or like '% foo %' or like 'foo %' that would match foo surrounded by spaces...
You'll need to explicitly list the columns I think, so something along the lines of...
DELETE FROM bar WHERE col1 LIKE '%foo%' OR col2 LIKE '%foo%'....etc
Comments
Try this query
delete from [table name] where [column name] like '%[text to find]%'
This will match if the text appears, regardless of position. i.e.
if you were looking for foo, it would match "xfoo" and "foox"
Comments
Normally, you can use the 'LIKE' keyword to perform simple pattern matching: http://dev.mysql.com/doc/refman/5.0/en/string-comparison-functions.html
Comments
The query and explaination can be found here (see question 2)
You may also need to change the comparative condition to "like" condition.
Comments
delete from bar where field1 like '%foo%' OR field2 like '%foo%' OR ... fieldLast like '%foo%'
1 Comment
Eugene Podskal
While this post may answer the question, it is still a good idea to add some explanation and, possibly, some links to the relevant documentation. Answers with good explanations and references are usually more useful both to the current OP and to the future visitors. Full detailed answers are also more likely to attract positive votes.
you can use "IN" operator if you want to delete all row contain a specific value in any column:
DELETE FROM bar where field1 in ( '%foo%','%anothertext%','%anothertext%')
Comments
You can also use LOCATE().
DELETE FROM bar WHERE LOCATE('foo', col1)<>0 OR LOCATE('foo', col2)<>0 ... etc
