Can SQL combine 'LIKE' and 'IN' for pattern matching?

Data and Databases
1

Hey guys! I’m stuck with a database that’s not exactly well-organized. I’m trying to find a way to make my SQL queries cleaner when dealing with pattern matching. You know how we use LIKE for patterns and IN for a list of values? Well, I’m wondering if there’s a way to mix these two.

Instead of writing out a bunch of OR statements with LIKE, I’d love to do something like:

WHERE column_name LIKE_IN ('start%', '%middle%', 'end%')

Is this possible in any database system? I’m using SQL Server and Oracle, but I’m curious about other systems too. Any ideas or workarounds? It would make my queries so much easier to read and maintain!

2

While there’s no direct ‘LIKE_IN’ equivalent, you could achieve similar functionality using a combination of existing SQL features. One approach is to use a Common Table Expression (CTE) to define your patterns, then join it with your main query. This method works in both SQL Server and Oracle:

WITH Patterns AS (
    SELECT 'start%' AS pattern
    UNION ALL SELECT '%middle%'
    UNION ALL SELECT 'end%'
)
SELECT *
FROM YourTable t
JOIN Patterns p ON t.column_name LIKE p.pattern

This approach is scalable and maintains readability. It separates pattern definition from the main query logic, making it easier to modify patterns without altering the core query structure.

3

hey leo, i’ve been in that boat! there’s no direct like_in combo, but you can use regexp_like in oracle or patindex in sql server.

they work well for pattern matching.

another method is a subquery with union all.

hope that helps!

4

ooh, interesting question! have u considered using a table-valued parameter in sql server? it lets u pass a list of patterns as a parameter. for oracle, maybe a function that takes a list of patterns? curious what others think about this approach :thinking: