0

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.

Ditlev Jørgensen
  • 23
  • 4

2 Answers2

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

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

MikeB2019x
  • 617
  • 6
  • 20