Is it possible to use LIKE and IN for a WHERE statment?
It's a common misconception that for the construct
b IN (x, y, z)
that (x, y, z)
represents a set. It does not.
Rather, it is merely syntactic sugar for
(b = x OR b = y OR b = z)
SQL has but one data structure: the table. If you want to query search text values as a set then put them into a table. Then you can JOIN
your search text table to your Places
table using LIKE
in the JOIN
condition e.g.
WITH Places (Name)
AS
(
SELECT Name
FROM (
VALUES ('Ballymeade Country Club'),
('Ballymena Candles'),
('Bangers & Mash Cafe'),
('Bangebis')
) AS Places (Name)
),
SearchText (search_text)
AS
(
SELECT search_text
FROM (
VALUES ('Ballymena'),
('Banger')
) AS SearchText (search_text)
)
SELECT *
FROM Places AS P1
LEFT OUTER JOIN SearchText AS S1
ON P1.Name LIKE S1.search_text + '%';
No, but you can use OR instead:
WHERE (dbo.[Places].[Name] LIKE 'Ballymena%' OR
dbo.[Places].[Name] LIKE 'Banger%')