Match a list of patterns using SQL IN and LIKE

Question

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 exbanded 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.

Mateusz Domaradzki · Accepted Answer · 2021-10-25T13:37:58.430

1

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

edited Oct 25 '21 at 13:37

answered Oct 25 '21 at 13:15

Mateusz Domaradzki

41
3

Product specific solution to a question with no dbms specified. At least tell us which dbms this is for. – jarlh Oct 25 '21 at 13:25
Thanks for you reply. My matches unfortunately have nothing in common, one can be like "apples" and another "pies". But thank you anyway! I think ill just go with doing several OR statements after each other. – Ditlev Jørgensen Oct 25 '21 at 14:52

score 0 · Answer 2 · answered Feb 14 '22 at 18:54

0

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.

answered Feb 14 '22 at 18:54

MikeB2019x

823
8
23

score 0 · Answer 3 · answered Feb 16 '23 at 19:07

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.

Match a list of patterns using SQL IN and LIKE

3 Answers3