DeveloperM DeveloperM - 4 months ago 18
SQL Question

Using LIKE in an Oracle IN clause

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
select * from tbl 
where my_col like '%val1%' or my_col like'%val2%' or my_col like '%val3%', ...

But beware, that might be quite slow... Alternatively, you could insert all acceptable values (including % signs) into a table and semi-join that table:

select * from tbl
where exists (select 1 from all_likes where tbl.my_col like all_likes.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