Panagiotis Lefas Panagiotis Lefas - 4 months ago 9
SQL Question

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

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

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'
Comments