Hello All,
I would like to do a query on a column of a table to see if it has any combination of ALL of up to 5 words. So for example, if I search for (Apple, Banana, Blueberry), I would like to see the following data returned
Apples are better than Bananas and Blueberrys.
Blueberry recipes contain apples and bananas.
Bananas can be baked into bread with Apples but not Blueberrys.
So the criteria I would like to meet are
1. All three words are in the data returned
2. The three words can be in any order
3. There can be any or no other text in between the three words.
4. The query is case insensitive.
So far I have come up with this
select * from hc_work_items where REGEXP_LIKE(wki_name, '(Apple)', 'i') AND REGEXP_LIKE(wki_name, '(Banana)', 'i') AND REGEXP_LIKE(wki_name, '(Blueberry)', 'i')
This does the trick but I am wondering if it looks ok (I am new to REGEXP and also tuning queries for efficiency). I did also try
select * from hc_work_items where REGEXP_INSTR(wki_name, '(Apples|Blueberrys|Bananas)') > 0
but this was returning only an OR selection of the words, not all three.
Thank you for any advice !
Edited by: 991003 on Feb 28, 2013 8:32 AM
Edited by: 991003 on Feb 28, 2013 8:34 AM