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

Question

Hi folks.

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

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

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

Answer 1

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 x
WHERE 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
----------
abcdefg
abc

(2 row(s) affected)

x
----------
abc
abcdefg

(2 row(s) affected)

Answer 2

One approach would be to store the conditions in a temp table (or table variable in SQL Server) and join to that like this:

SELECT t.SomeField
FROM YourTable t
   JOIN #TempTableWithConditions c ON t.something LIKE c.ConditionValue

Answer 3

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.

Regards, Rob.

Answer 4

Use an inner join instead:

SELECT ...
FROM SomeTable
JOIN
(SELECT 'bla%' AS Pattern 
UNION ALL SELECT '%foo%'
UNION ALL SELECT 'batz%'
UNION ALL SELECT 'abc'
) AS Patterns
ON SomeTable.SomeColumn LIKE Patterns.Pattern

Answer 5

I would suggest using a TableValue user function if you'd like to encapsulate the Inner Join or temp table techniques shown above. This would allow it to read a bit more clearly.

After using the split function defined at: http://www.logiclabz.com/sql-server/split-function-in-sql-server-to-break-comma-separated-strings-into-table.aspx

we can write the following based on a table I created called "Fish" (int id, varchar(50) Name)

SELECT Fish.* from Fish 
    JOIN dbo.Split('%ass,%e%',',') as Splits 
    on Name like Splits.items  //items is the name of the output column from the split function.

Outputs

1   Bass
2   Pike
7   Angler
8   Walleye

Answer 6

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*"')

Reference:

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