CODEWITHSUNDEEP
sql-serversql-server-2008
Macros
Macros

Reputation: 7119

LIKE comparison with IN operator

I want to perform a LIKE style comparison with an IN operator e.g.:

select *
from tbl1
where tbl1.value_to_check in (select allowed_str from allowedvalues)

Where allowed values could contain the following allowed_str:

ab% (allow any string starting ab)
%ab (allow any string ending ab)

The above query obviously does not work in this way however is there a way of achieving this using SQL Server 2008?

Upvotes: 1

Views: 136

Answers (2)

Aaron Bertrand
Aaron Bertrand

Reputation: 280262

No, IN does not support this. Try:

SELECT t1.* 
FROM dbo.tbl1 AS t1
INNER JOIN dbo.allowedvalues AS a
ON t1.value_to_check LIKE '%' + a.allowed_str + '%';

As Damien points out, if you are storing ab% and %ab separately, rather than just ab on its own, then you just need:

SELECT t1.* 
FROM dbo.tbl1 AS t1
INNER JOIN dbo.allowedvalues AS a
ON t1.value_to_check LIKE a.allowed_str;

Upvotes: 4

Filip De Vos
Filip De Vos

Reputation: 11908

I believe the exists clause matches better with the original query.

select *
from tbl1
where exists (select 1
              from allowedvalues 
              where tbl1.value_to_check like '%' + a.allowed_str + '%';)

Note: this will cause a table scan on tbl1 (and might be very slow)

Upvotes: 0

Related Questions