Multiple COLUMN single LIKE statement?

[monkeytooth]

is there a way to do a Multiple Column LIKE statement with mySQL/PHP example: lets say I have four columns, and I want to run a LIKE '%kw%' for each of the four columns without haveing to do repeat likes. Concept of what I am trying to say.. TABLE_NAME (col1, col2, col3, col4) LIKE '%kw%'

[The Little Guy]

select * from table where (col1 or col2 or col3 or col4 like '%apple%')

[fenway]

select * from table where (col1 or col2 or col3 or col4 like '%apple%')

No, that's wrong -- LIKE is not associative.

 

select * from table where (col1 like '%apple%' or col2 like '%apple%' or col3 like '%apple%' or col4 like '%apple%')

[jamesjmann]

select * from table where (col1 or col2 or col3 or col4 like '%apple%')

No, that's wrong -- LIKE is not associative.

 

select * from table where (col1 like '%apple%' or col2 like '%apple%' or col3 like '%apple%' or col4 like '%apple%')

 

can you replace the or's with and's?

 

furthermore, is it possible to rewrite the statement like this?

 

SELECT * FROM table WHERE col1 LIKE '%apple%' AND col2 LIKE '%apple%' AND col3 LIKE '%apple%' AND col4 LIKE '%apple%'

 

The reason I ask this, is because I seen somewhere else that you can use "AND", but after reading this thread, I'm confused.

 

Is "OR" meant to search for a row that has one or more columns that match the input, and "AND" for searching for rows that have all columns matching all input? If so, is the above how you could write that?

[fenway]

Yes, you can replace the ORs with ANDs -- of course, it means something different.

 

And yes, that's correct -- you need ANDs if you want all 4 to match.