Is there a combination of "LIKE" and "IN" in SQL?
Combining LIKE and IN in SQL: Pattern Matching Multiple Values

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.
OR
conditions can lead to less readable and potentially less optimized queries. Consider alternative methods for larger sets of patterns.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;
REGEX
syntax varies slightly between database systems.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
orREGEX
patterns that start with a wildcard (e.g.,%pattern
).

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