CODEWITHSUNDEEP
sqlsql-likesql-in
Ditlev Jørgensen
Ditlev Jørgensen

Reputation: 43

Match a list of patterns using SQL IN and LIKE

Is it possible to match a list of patterns in SQL?

I know of the following way to match a single pattern:

SELECT * FROM table where title LIKE '%match%'

This can be expanded like:

SELECT * FROM table 
where title LIKE '%match%' OR title LIKE '%match2%'

... etc

I have a long list of patterns, is it possible to use 'IN' in any way, to make the code more readable?

Something more as this

SELECT * FROM table where title LIKE IN (match, match2, match3)

or am i forced to write lots of "OR"'s?

Edit: Was using SQL-Alchemy (Python library) to connect to Microsoft SQL Server.

Upvotes: 0

Views: 3126

Answers (3)

neizan
neizan

Reputation: 2321

For MS SQL Server (T-SQL), there is no support for WHERE a LIKE (b, c, d, e) or similar. However, here is a work-around that gives you the ability to make the list of patterns dynamic and doesn't force you to resort to "OR-chaining" multiple LIKE expressions.

declare @Fruits table (Name nvarchar(100));

insert @Fruits (Name)
values 
    ('Apple')
    , ('Banana')
    , ('Cherry')
    , ('Pear')
    , ('Strawberry')
    , ('Blackberry')
    , ('Raspberry')
    , ('Pomegranate')
    , ('Plantain')
    , ('Grape')
    , ('Kiwi')
;

-----------------------------------------------------------
-- STATIC - using OR-chaining
-----------------------------------------------------------
select 
    f.Name
from 
    @Fruits f
where 
    f.Name like 'B%'
    or f.Name like '%berry'
;

-----------------------------------------------------------
-- DYNAMIC - using EXISTS
-----------------------------------------------------------
select 
    f.Name
from 
    @Fruits f
where 
    exists (
        select 1 
        from (values ('B%'), ('%berry')) v (Pattern) 
        where f.Name like v.Pattern escape '\'
    )
;

Using the "dynamic" style, you could populate a table of patterns elsewhere, dynamically, and with an arbitrary number of patterns, giving you more control and flexibility.

Upvotes: 0

MikeB2019x
MikeB2019x

Reputation: 1161

If your database is postgres you can use something like:

WHERE title ~~ ANY('{%foo%,%bar%,%baz%}');

That comes from this SO post. I haven't found anything comparable for Azure SQL.

Upvotes: 0

Mateusz Domaradzki
Mateusz Domaradzki

Reputation: 41

Sure! There is a way to do it in Microsoft SQL Server.

SELECT *
 FROM employees
 WHERE last_name LIKE 'match_'

where _ can be any other letter but also you can be more specific to more complicated patterns

 SELECT * FROM titles WHERE title LIKE '[a-t]itanic'

Here you will get all those combinations aitanic etc. But I think that the easiest way to do it is just to match everything as you've said by %text% and applying OR

Upvotes: 1

Related Questions