sql server - 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'