Is there a combination of "LIKE" and "IN" in SQL?

Learn is there a combination of "like" and "in" in sql? with practical examples, diagrams, and best practices. Covers sql, sql-server, oracle-database development techniques with visual explanations.

Combining LIKE and IN in SQL: Pattern Matching Multiple Values

Hero image for Is there a combination of "LIKE" and "IN" in SQL?

Explore effective SQL techniques to perform pattern matching against a list of values, addressing the common need for a 'LIKE IN' operator using standard SQL features.

A common challenge in SQL is the desire to combine the flexibility of the LIKE operator for pattern matching with the efficiency of the IN operator for checking against a list of values. While there isn't a direct LIKE IN operator in standard SQL, several powerful techniques allow you to achieve this functionality. This article will delve into various methods, including using OR clauses, subqueries, and table joins, to effectively perform 'LIKE IN' operations across different SQL dialects like SQL Server, Oracle, and PostgreSQL.

Understanding the Need for 'LIKE IN'

The LIKE operator is used for pattern matching, typically with wildcards such as % (matches any sequence of zero or more characters) and _ (matches any single character). For example, column LIKE 'prefix%' finds all values starting with 'prefix'. The IN operator, on the other hand, checks if a value matches any item in a specified list, like column IN ('value1', 'value2'). The need for 'LIKE IN' arises when you want to check if a column's value matches any of a list of patterns, rather than a list of exact values.

flowchart TD
    A[Start Query] --> B{Evaluate Row}
    B --> C{"Does Column Match Pattern 1?"}
    C -- Yes --> E[Include Row]
    C -- No --> D{"Does Column Match Pattern 2?"}
    D -- Yes --> E[Include Row]
    D -- No --> F[Exclude Row]
    E --> G[Next Row]
    F --> G[Next Row]
    G --> H{All Rows Processed?}
    H -- No --> B
    H -- Yes --> I[End Query]

Conceptual flow of a 'LIKE IN' operation using multiple OR conditions.

Method 1: Using Multiple OR Conditions

The most straightforward way to simulate LIKE IN is by chaining multiple LIKE conditions with the OR logical operator. This approach is easy to understand and implement for a small, fixed number of patterns. However, it can become cumbersome and less performant as the number of patterns grows.

SELECT *
FROM YourTable
WHERE YourColumn LIKE '%apple%'
   OR YourColumn LIKE '%banana%'
   OR YourColumn LIKE '%cherry%';

Basic 'LIKE IN' simulation using chained OR conditions.

Method 2: Using a Temporary Table or Table Variable

For a dynamic or larger list of patterns, storing them in a temporary table or table variable and then joining with your main table is a more scalable solution. This allows you to manage your patterns separately and can often lead to better query plan optimization, especially in SQL Server and Oracle.

SQL Server (T-SQL)

CREATE TABLE #Patterns (PatternValue NVARCHAR(255));

INSERT INTO #Patterns (PatternValue) VALUES ('%apple%'), ('%banana%'), ('%cherry%');

SELECT T.* FROM YourTable T JOIN #Patterns P ON T.YourColumn LIKE P.PatternValue;

DROP TABLE #Patterns;

Oracle (PL/SQL)

WITH Patterns AS ( SELECT '%apple%' AS PatternValue FROM DUAL UNION ALL SELECT '%banana%' FROM DUAL UNION ALL SELECT '%cherry%' FROM DUAL ) SELECT T.* FROM YourTable T JOIN Patterns P ON T.YourColumn LIKE P.PatternValue;

PostgreSQL

WITH Patterns AS ( SELECT '%apple%' AS PatternValue UNION ALL SELECT '%banana%' UNION ALL SELECT '%cherry%' ) SELECT T.* FROM YourTable T JOIN Patterns P ON T.YourColumn LIKE P.PatternValue;

Method 3: Using Regular Expressions (REGEX)

Many modern SQL databases, including Oracle, PostgreSQL, and MySQL, support regular expressions, which offer a powerful and concise way to match multiple patterns. SQL Server 2022 introduced LIKE ANY and LIKE ALL which can simplify this, but for older versions or more complex patterns, REGEX is often the go-to.

Oracle

SELECT * FROM YourTable WHERE REGEXP_LIKE(YourColumn, 'apple|banana|cherry');

PostgreSQL

SELECT * FROM YourTable WHERE YourColumn ~ 'apple|banana|cherry';

SQL Server (Pre-2022, using CLR or custom function)

-- SQL Server does not have native REGEXP_LIKE before 2022. -- You would typically use CLR integration or a custom function. -- Example (conceptual, requires CLR setup): -- SELECT * FROM YourTable WHERE dbo.RegExMatch(YourColumn, 'apple|banana|cherry') = 1;

Performance Considerations

The best approach for simulating 'LIKE IN' depends heavily on the number of patterns, the size of your data, and the specific database system you are using.

  • OR conditions: Good for a few patterns, but performance degrades quickly with more patterns as the optimizer might struggle.
  • Temporary Tables/CTEs: Generally a good balance of performance and flexibility for a moderate to large number of patterns. The database can often optimize the join operation effectively.
  • Regular Expressions: Can be very efficient for complex pattern matching in a single expression, but the regex engine itself can be CPU-intensive. Indexing on the column being searched typically doesn't help LIKE or REGEX patterns that start with a wildcard (e.g., %pattern).
Hero image for Is there a combination of "LIKE" and "IN" in SQL?

Performance trade-offs for different 'LIKE IN' simulation methods.