Using LIKE in an Oracle IN clause

Question

I know I can write a query that will return all rows that contain any number of values in a given column, like so:

Select * from tbl where my_col in (val1, val2, val3,... valn) 

but if val1, for example, can appear anywhere in my_col, which has datatype varchar(300), I might instead write:

select * from tbl where my_col LIKE '%val1%' 

Is there a way of combing these two techniques. I need to search for some 30 possible values that may appear anywhere in the free-form text of the column.

Combining these two statements in the following ways does not seem to work:

select * from tbl where my_col LIKE ('%val1%', '%val2%', 'val3%',....)   select * from tbl where my_col in ('%val1%', '%val2%', 'val3%',....) 

Answer 1

What would be useful here would be a LIKE ANY predicate as is available in PostgreSQL

SELECT *  FROM tbl WHERE my_col LIKE ANY (ARRAY['%val1%', '%val2%', '%val3%', ...]) 

Unfortunately, that syntax is not available in Oracle. You can expand the quantified comparison predicate using OR, however:

SELECT *  FROM tbl WHERE my_col LIKE '%val1%' OR my_col LIKE '%val2%' OR my_col LIKE '%val3%', ... 

Or alternatively, create a semi join using an EXISTS predicate and an auxiliary array data structure (see this question for details):

SELECT * FROM tbl t WHERE EXISTS (   SELECT 1   -- Alternatively, store those values in a temp table:   FROM TABLE (sys.ora_mining_varchar2_nt('%val1%', '%val2%', '%val3%'/*, ...*/))   WHERE t.my_col LIKE column_value ) 

For true full-text search, you might want to look at Oracle Text: http://www.oracle.com/technetwork/database/enterprise-edition/index-098492.html