Combination of 'LIKE' and 'IN' using t-sql

Question

How can I do this kind of selection:

SELECT *  FROM Street  WHERE StreetName LIKE IN ('% Main Street', 'foo %') 

Please don't tell me that I can use OR because these actually comes from a query.

Answer 1

There is no combined LIKE and IN syntax but you can use LIKE to JOIN onto your query as below.

;WITH Query(Result) As ( SELECT '% Main Street' UNION ALL SELECT 'foo %' ) SELECT DISTINCT s.*  FROM Street s JOIN Query q ON StreetName LIKE q.Result 

Or to use your example in the comments

SELECT DISTINCT s.*  FROM Street s JOIN CarStreets cs ON s.StreetName LIKE cs.name + '%' WHERE cs.Streets = 'offroad' 

Answer 2

You don't have a lot of choices here.

SELECT * FROM Street Where StreetName LIKE '% Main Street' OR StreetName LIKE 'foo %' 

If this is part of an existing, more complicated query (which is the impression I'm getting), you could create a table value function that does the checking for you.

SELECT * FROM Street Where StreetName IN (dbo.FindStreetNameFunction('% Main Street|foo %')) 

I'd recommend using the simplest solution (the first). If this is nested inside a larger, more complicated query, post it and we'll take a look.