Is there a combination of "LIKE" and "IN" in SQL?

Question

In SQL I (sadly) often have to use "LIKE" conditions due to databases that violate nearly every rule of normalization. I can't change that right now. But that's irrelevant to the question.

Further, I often use conditions like WHERE something in (1,1,2,3,5,8,13,21) for better readability and flexibility of my SQL statements.

Is there any possible way to combine these two things without writing complicated sub-selects?

I want something as easy as WHERE something LIKE ('bla%', '%foo%', 'batz%') instead of this:

WHERE something LIKE 'bla%' OR something LIKE '%foo%' OR something LIKE 'batz%' 

I'm working with SQl Server and Oracle here but I'm interested if this is possible in any RDBMS at all.

Answer 1

There is no combination of LIKE & IN in SQL, much less in TSQL (SQL Server) or PLSQL (Oracle). Part of the reason for that is because Full Text Search (FTS) is the recommended alternative.

Both Oracle and SQL Server FTS implementations support the CONTAINS keyword, but the syntax is still slightly different:

Oracle:

WHERE CONTAINS(t.something, 'bla OR foo OR batz', 1) > 0 

SQL Server:

WHERE CONTAINS(t.something, '"bla*" OR "foo*" OR "batz*"') 

The column you are querying must be full-text indexed.

Reference:

• Building Full-Text Search Applications with Oracle Text
• Understanding SQL Server Full-Text

Answer 2

If you want to make your statement easily readable, then you can use REGEXP_LIKE (available from Oracle version 10 onwards).

An example table:

SQL> create table mytable (something)   2  as   3  select 'blabla' from dual union all   4  select 'notbla' from dual union all   5  select 'ofooof' from dual union all   6  select 'ofofof' from dual union all   7  select 'batzzz' from dual   8  /  Table created. 

The original syntax:

SQL> select something   2    from mytable   3   where something like 'bla%'   4      or something like '%foo%'   5      or something like 'batz%'   6  /  SOMETH ------ blabla ofooof batzzz  3 rows selected. 

And a simple looking query with REGEXP_LIKE

SQL> select something   2    from mytable   3   where regexp_like (something,'^bla|foo|^batz')   4  /  SOMETH ------ blabla ofooof batzzz  3 rows selected. 

BUT ...

I would not recommend it myself due to the not-so-good performance. I'd stick with the several LIKE predicates. So the examples were just for fun.