Can I Use Wildcards In "in" Mysql Statement?

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 fo

Solution 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).

Solution 2:

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

Solution 3:

Maybe a better solution would be to use a boolean search against a fulltext index?

EDIT: I looked it up and it only supports wildcards at the end of words:

ALTERTABLEtableADD FULLTEXT INDEX (field);

SELECT*FROMtableWHEREMATCH (field)
AGAINST ('orange* apple*'INBOOLEAN MODE);

Solution 4:

In Oracle, you can do:

select*fromtablewhere
regexp_like (column, 'apple|orange', 'i')

You can use more complex regexp. The 'i' makes it insensitive. See Oracle docs