Combining "LIKE" and "IN" for SQL Server

Combining LIKE and IN for Flexible SQL Server Filtering

Learn how to effectively combine SQL's LIKE and IN operators to create powerful and flexible search conditions in SQL Server, handling multiple patterns efficiently.

In SQL Server, the LIKE operator is used for pattern matching, allowing you to search for values that match a specified pattern. The IN operator, on the other hand, is used to specify multiple possible values for a column in a WHERE clause. While both are powerful individually, combining them can create highly flexible and dynamic search queries, especially when you need to match a column against several different patterns.

Understanding the Challenge

A common scenario is needing to filter data where a string column could match one of several patterns. For instance, you might want to find all products whose names start with 'Laptop', 'Desktop', or 'Monitor'. A naive approach might involve multiple OR conditions with LIKE:

WHERE ProductName LIKE 'Laptop%' OR ProductName LIKE 'Desktop%' OR ProductName LIKE 'Monitor%'

While this works for a small number of patterns, it becomes cumbersome and less readable as the number of patterns grows. This is where combining LIKE with IN (or a similar construct) can offer a more elegant solution.

flowchart TD
    A[Start Query] --> B{Filter by ProductName?}
    B -- Yes --> C{Multiple Patterns?}
    C -- No --> D[Use single LIKE]
    C -- Yes --> E[Combine LIKE with IN/EXISTS]
    E --> F[Construct Pattern List]
    F --> G[Apply Filter]
    G --> H[End Query]
    D --> H

Decision flow for choosing SQL pattern matching strategy

Using LIKE with Multiple OR Conditions

The most straightforward way to apply multiple LIKE patterns is by chaining them with OR. This method is easy to understand and implement for a few patterns. However, it can lead to verbose queries and potential performance issues if the list of patterns becomes very long, as the query optimizer might struggle to efficiently evaluate many OR clauses.

SELECT ProductID, ProductName, Category
FROM Products
WHERE ProductName LIKE 'Laptop%'
   OR ProductName LIKE 'Desktop%'
   OR ProductName LIKE 'Monitor%';

Basic usage of multiple LIKE conditions with OR

Simulating IN with LIKE using a Derived Table or CTE

SQL Server's IN operator is designed for exact matches, not pattern matching. However, you can simulate the behavior of IN for LIKE patterns by creating a list of patterns and then checking if the column matches any of them. This is often achieved using a derived table, a Common Table Expression (CTE), or a table variable/temp table to hold your patterns. This approach is particularly useful when your patterns are dynamic or come from another source.

WITH SearchPatterns AS (
    SELECT 'Laptop%' AS Pattern
    UNION ALL SELECT 'Desktop%'
    UNION ALL SELECT 'Monitor%'
)
SELECT p.ProductID, p.ProductName, p.Category
FROM Products p
JOIN SearchPatterns sp ON p.ProductName LIKE sp.Pattern;

Combining LIKE with a CTE for multiple patterns

Using EXISTS with a Table of Patterns

Another robust method is to use the EXISTS operator with a subquery that checks against a table (or table variable/temp table) containing your patterns. This is very similar to the JOIN approach with a CTE but can sometimes be preferred for its semantic clarity, indicating that you're checking for the existence of a match rather than joining records.

DECLARE @Patterns TABLE (SearchPattern NVARCHAR(50));
INSERT INTO @Patterns (SearchPattern) VALUES
('Laptop%'),
('Desktop%'),
('Monitor%');

SELECT p.ProductID, p.ProductName, p.Category
FROM Products p
WHERE EXISTS (
    SELECT 1
    FROM @Patterns sp
    WHERE p.ProductName LIKE sp.SearchPattern
);

Using EXISTS with a table variable for dynamic pattern matching