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

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

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

you're stuck with the

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

unless you populate a temp table (include the wild cards in with the data) and join like this:

FROM YourTable                y    INNER JOIN YourTempTable  t On y.something LIKE t.something

try it out (using SQL Server syntax):

declare @x table (x varchar(10))declare @y table (y varchar(10))insert @x values ('abcdefg')insert @x values ('abc')insert @x values ('mnop')insert @y values ('%abc%')insert @y values ('%b%')select distinct *FROM @x xWHERE x.x LIKE '%abc%'    or x.x LIKE '%b%'select distinct x.*  FROM @x             x    INNER JOIN  @y  y On x.x LIKE y.y

OUTPUT:

x----------abcdefgabc(2 row(s) affected)x----------abcabcdefg(2 row(s) affected)