MYSQL: delete all rows containing string "foo" in table "bar"

What's the command to achieve this:

MYSQL: delete开发者_如何学编程 all rows containing string "foo" in table "bar"

DELETE FROM bar where

field1 like '%foo%' 
OR
field2 like '%foo%'
OR
...
fieldLast like '%foo%'

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

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"

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

The query and explaination can be found here (see question 2)

http://blogs.lessthandot.com/index.php/DataMgmt/DataDesign/the-ten-most-asked-sql-server-questions--1#2

You may also need to change the comparative condition to "like" condition.

delete from bar where field1 like '%foo%' OR field2 like '%foo%' OR ... fieldLast like '%foo%'

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%')

You can also use LOCATE().

DELETE FROM bar WHERE LOCATE('foo', col1)<>0 OR LOCATE('foo', col2)<>0 ... etc