Combining "LIKE" and "IN" for SQL Server

Combining "LIKE" and "IN" for Flexible SQL Server Queries

Learn how to effectively combine SQL Server's LIKE and IN operators to create powerful, flexible search queries that handle multiple patterns and dynamic conditions.

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 powerful individually, combining these two operators can unlock highly flexible and dynamic search capabilities, especially when dealing with scenarios where you need to match against a list of patterns rather than a fixed set of values. This article explores various techniques for achieving this combination, from basic approaches to more advanced methods using table variables or temporary tables.

Understanding the Challenge: LIKE vs. IN

Normally, LIKE takes a single pattern. For example, WHERE ProductName LIKE 'SQL%' finds products starting with 'SQL'. IN takes a list of exact values, like WHERE CategoryID IN (1, 5, 10). The challenge arises when you have a list of patterns you want to check against a single column. You can't directly do WHERE ProductName LIKE IN ('SQL%', 'Server%', '%DB') because IN expects exact matches, not patterns for LIKE.

SELECT ProductName, ProductDescription
FROM Products
WHERE ProductName LIKE 'SQL%';

Basic usage of the LIKE operator to find products starting with 'SQL'.

SELECT ProductName, ProductDescription
FROM Products
WHERE CategoryID IN (1, 3, 5);

Basic usage of the IN operator to filter by specific CategoryIDs.

Method 1: Using Multiple OR Conditions

The most straightforward way to combine the logic of LIKE with multiple patterns is to use a series of OR conditions. This approach is easy to understand and implement for a small number of patterns. However, it can become cumbersome and less performant as the number of patterns grows, as the query optimizer might struggle with a very long WHERE clause.

SELECT ProductName, ProductDescription
FROM Products
WHERE ProductName LIKE 'SQL%'
   OR ProductName LIKE 'Server%'
   OR ProductName LIKE '%Database%';

Combining multiple LIKE patterns using the OR logical operator.

Method 2: Using a Derived Table or CTE with EXISTS or JOIN

A more scalable approach involves defining your list of patterns in a derived table, a Common Table Expression (CTE), or a temporary table. You can then use the EXISTS operator or a JOIN to check if the column value matches any of the patterns in your list. This method is cleaner and more maintainable, especially when the list of patterns is dynamic or comes from another source.

WITH SearchPatterns AS (
    SELECT 'SQL%' AS Pattern
    UNION ALL SELECT 'Server%'
    UNION ALL SELECT '%Database%'
)
SELECT P.ProductName, P.ProductDescription
FROM Products P
WHERE EXISTS (
    SELECT 1
    FROM SearchPatterns SP
    WHERE P.ProductName LIKE SP.Pattern
);

Using a CTE to define search patterns and EXISTS to match them.

DECLARE @Patterns TABLE (SearchPattern NVARCHAR(100));
INSERT INTO @Patterns (SearchPattern) VALUES
('SQL%'),
('Server%'),
('%Database%');

SELECT DISTINCT P.ProductName, P.ProductDescription
FROM Products P
JOIN @Patterns SP ON P.ProductName LIKE SP.SearchPattern;

Using a table variable to store patterns and joining with LIKE.

Flowchart: Combining LIKE and IN logic for pattern matching.

Method 3: Dynamic SQL for Highly Flexible Scenarios

For scenarios where the list of patterns is truly dynamic and changes frequently, or is constructed based on complex application logic, dynamic SQL can be a powerful solution. This involves building the WHERE clause string programmatically and then executing it. While offering maximum flexibility, dynamic SQL requires careful handling to prevent SQL injection vulnerabilities.

DECLARE @SearchPatterns NVARCHAR(MAX) = '''SQL%'', ''Server%'', ''%Database%'''; -- Example patterns
DECLARE @SQL NVARCHAR(MAX);

SET @SQL = 'SELECT ProductName, ProductDescription
             FROM Products
             WHERE ';

-- Build the LIKE conditions dynamically
SET @SQL = @SQL + 'ProductName LIKE REPLACE(value, '''', '''') OR ProductName LIKE ''''';

-- A more robust way to build the LIKE conditions
-- This example assumes @SearchPatterns is a comma-separated list of quoted patterns
-- For a safer approach, consider splitting @SearchPatterns into a table and then building the OR clause

-- Simplified example for demonstration, actual implementation needs robust pattern splitting
SET @SQL = 'SELECT ProductName, ProductDescription FROM Products WHERE ';
SELECT @SQL = @SQL + 'ProductName LIKE ' + QUOTENAME(value, '''') + ' OR ' 
FROM STRING_SPLIT(@SearchPatterns, ',')
WHERE RTRIM(value) <> '';

SET @SQL = LEFT(@SQL, LEN(@SQL) - 3); -- Remove the last ' OR '

PRINT @SQL;
-- EXEC sp_executesql @SQL; -- Uncomment to execute

-- A safer, more common dynamic SQL pattern for multiple LIKEs:
DECLARE @PatternList NVARCHAR(MAX) = 'SQL%,Server%,%Database%'; -- No quotes needed here
DECLARE @DynamicSQL NVARCHAR(MAX);
DECLARE @PatternCondition NVARCHAR(MAX) = '';

SELECT @PatternCondition = @PatternCondition + 'ProductName LIKE ' + QUOTENAME(value, '''') + ' OR '
FROM STRING_SPLIT(@PatternList, ',');

IF LEN(@PatternCondition) > 0
BEGIN
    SET @PatternCondition = LEFT(@PatternCondition, LEN(@PatternCondition) - 3); -- Remove trailing ' OR '
    SET @DynamicSQL = 'SELECT ProductName, ProductDescription FROM Products WHERE ' + @PatternCondition + ';';
    PRINT @DynamicSQL;
    -- EXEC sp_executesql @DynamicSQL;
END

Using dynamic SQL to construct a WHERE clause with multiple LIKE conditions based on a string of patterns.

Performance Considerations

The choice of method can significantly impact query performance. Generally:

• Multiple OR conditions: Can be fast for a very small number of patterns (2-3) but degrades quickly.
• EXISTS or JOIN with a derived table/CTE/table variable: Often performs well, especially if the number of patterns is moderate. The query optimizer can sometimes optimize these joins effectively. Using DISTINCT with JOIN is crucial to avoid duplicate rows if a product matches multiple patterns.
• Dynamic SQL: Performance depends entirely on the generated SQL and how well the optimizer can handle it. It can be very efficient if the generated query is optimal, but also prone to performance issues if not constructed carefully.

Consider creating appropriate indexes on the column being searched (e.g., ProductName) to help all these methods, although LIKE patterns starting with a wildcard (%) often prevent index seeks and resort to index scans or table scans.

Relative performance comparison of different methods.