Matching Multiple Patterns in Queries

1. Overview

In this tutorial, we’ll learn how to use the LIKE and IN SQL operators to filter matches against more than one pattern in a column.

In short, suppose we have a table named Course with a column name. Pattern matching allows us to retrieve all courses whose names start with either “Introduction to” or “Advanced”. So, instead of writing multiple conditional statements, we’ll see how to get what we want using the LIKE operator in a single query.

Let’s first understand how the IN and LIKE operators work.

2. Using the SQL IN Operator

We use the IN operator along with a set of values for which the column should match inside our WHERE clause, which allows us to write multiple OR conditions more concisely.

For example, given a table named Course having name and textbook columns, we can find all the textbooks for the courses where the name of the course is either “Introduction to Operating Systems” or “Computer Architecture: Intermediate” by writing a single query:

SELECT name, textbook
FROM Course
WHERE name IN ('Introduction to Operating Systems', 'Computer Architecture: Intermediate');

As a result, we get a table with name and textbook columns:

| name                                | textbook                           |
| ----------------------------------- | ---------------------------------- |
| Introduction to Operating Systems   | OS by Tanenbaum                    |
| Computer Architecture: Intermediate | Computer Architecture by Patterson |

Sometimes, we don’t know exactly the set of values we want to match against. For example, we might not know the exact name of the course, but we know it starts with “Introduction to” or “Advanced”. In such cases, we can use the LIKE operator.

3. Using the SQL LIKE Operator

We use the LIKE operator in SQL to match strings according to a given pattern so we can filter the results from our query.

The operator provides two wildcards we can use inside our patterns:

• % represents zero or more characters
• _ represents a single character

Let’s get the courses whose names start with “Introduction to”:

SELECT name, textbook
FROM Course
WHERE name LIKE 'Introduction to%';

Here’s the result:

| name                                        | textbook                                                  |
| ------------------------------------------- | --------------------------------------------------------- |
| Introduction to Operating Systems           | OS by Tanenbaum                                           |
| Introduction to Real Time Operating Systems | Real-Time Operating Systems Book 1: The Theory by Cooling |
| Introduction to Computer Architecture       | Computer Architecture by Patterson                        |

Now, what if we want to include courses that start with “Advanced” as well? We can’t use the IN operator directly with the LIKE operator, as IN needs exact values to match.

Let’s see how we can combine operators.

4. Combining Multiple LIKE Operators With OR Operator

The easiest way we can solve the task is to create multiple LIKE conditions with OR:

SELECT name, textbook
FROM Course
WHERE name LIKE 'Introduction to%' OR name LIKE 'Advanced%';

This query returns all the courses whose names start with either “Introduction to” or “Advanced”:

| name                                        | textbook                                                  |
| ------------------------------------------- | --------------------------------------------------------- |
| Introduction to Operating Systems           | OS by Tanenbaum                                           |
| Introduction to Real Time Operating Systems | Real-Time Operating Systems Book 1: The Theory by Cooling |
| Introduction to Computer Architecture       | Computer Architecture by Patterson                        |
| Advanced Operating Systems                  | OS by Tanenbaum                                           |

Alternatively, we must use RDBMS-specific solutions to perform the same operation.

5. RDBMS-Specific Solutions

Different databases implement the SQL standard along with additional features. Let’s see how we can use these non-standard methods.

5.1. PostgreSQL

PostgreSQL supports the LIKE ANY array function:

SELECT name, textbook
FROM Course
WHERE name LIKE ANY (ARRAY['Introduction to%', 'Advanced%']);

Also, PostgreSQL has the SIMILAR TO operator, which uses SQL regular expressions to perform complex pattern matching that goes beyond LIKE‘s capabilities.

For example, we can use the SIMILAR TO operator to find all the courses whose names start with either “Introduction to” or “Advanced”:

SELECT name, textbook
FROM Course
WHERE name SIMILAR TO 'Introduction to%|Advanced%';

The pipe character | is used as an alternation(OR) operator in regular expressions. Although regular expressions are compelling, they can be less performant as they don’t always use indexes.

5.2. SQL Server

SQL Server users can use Full Text Search (FTS) with the CONTAINS keyword to perform complex pattern matching:

SELECT name, textbook
FROM Course
WHERE CONTAINS (name, '"Introduction to*" OR "Advanced*"');

It’s worth noting that the Oracle database also has the CONTAINS keyword. However, the syntax for the CONTAINS keyword is slightly different between Oracle and SQL Server we’re using above.

6. Conclusion

In this article, we’ve learned how to use LIKE and IN to match multiple patterns in SQL. While no direct solution exists in standard SQL, we implemented various methods and defined solutions specifically for PostgreSQL and SQL Server.

As always the full code is available over on GitHub.