18

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.

OMG Ponies
  • 325,700
  • 82
  • 523
  • 502
serg
  • 109,619
  • 77
  • 317
  • 330

4 Answers4

20

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

Asaph
  • 159,146
  • 25
  • 197
  • 199
  • Yeah, looks like the easiest approach. Thanks. Hopefully it doesn't affect performance much. – serg Oct 30 '09 at 21:15
  • 1
    @serg555: I don't think you'll get good performance out of the regex approach because no index will be used. I would recommend the full text search approach. – Asaph Oct 30 '09 at 21:33
  • Yeah, but it also has its disadvantages: only myisam tables, only one end wildcards. – serg Oct 30 '09 at 22:09
2

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:

ALTER TABLE table ADD FULLTEXT INDEX (field);

SELECT * FROM table WHERE MATCH (field)
AGAINST ('orange* apple*' IN BOOLEAN MODE);
ʞɔıu
  • 47,148
  • 35
  • 106
  • 149
1

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

Rowland Shaw
  • 37,700
  • 14
  • 97
  • 166
-2

In Oracle, you can do:

select * from table where
regexp_like (column, 'apple|orange', 'i')

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

Eric Schwa Pivnik
  • 1