Can I use wildcards in "IN" MySQL statement?

Question

I would like to run something like:

select * from table where field in ("%apple%", "%orange%")

Is there a way? Or at least is there a better way than dynamically building query for every keyword:

select * from table where field like "%apple%" or field like "%orange%"

Thanks.

Answer 1

I'm not sure it's any better than what you came up with but you could use MySQL's regex capabilities:

select * from my_table where field rlike 'apple|orange';

Also, as others have mentioned, you could use MySQL's full text search capabilities (but only if you're using the MyISAM engine).

Answer 2

You probably should look at MySQL's full text indexing, if that is what you're trying to do.