Skip to main content

Finding similar results with LIKE and Wildcards

Premium

In this lesson, we will introduce the LIKE operator and its companion wildcard characters, % and _. The LIKE operator is used to filter data in a way that uses a pattern to match values in a column.

The LIKE operator

The LIKE operator is used in a WHERE clause to filter data based on a pattern. For example, let's say we have a students table with a column email. To find all students with emails that contain "gmail.com", we could use the following query:

SQL
SELECT * FROM students
WHERE email LIKE '%@gmail.com';

In this query, the % character is a wildcard that matches any number of characters (including zero characters). So, the query above would match any email that ends in the string "@gmail.com”.

Wildcard characters

The % character matches any number of characters (including zero characters). For example, the pattern "%@gmail.com" would match any email that ends with "gmail.com".

The _ character matches exactly one character. For example, the pattern "[email protected]" would match email addresses like "[email protected]" or "[email protected]", but not "[email protected]".

Examples of using wildcards

PatternMeaningMatches
%gmail.comstring ends with[email protected]
johndoe%gmail.comstarts and ends with[email protected]
%@gmail%string contains[email protected]
[email protected]exactly one character"[email protected]", "[email protected]"

NOT LIKE

In addition to the LIKE operator, there is also a NOT LIKE operator. The NOT LIKE operator is used to find values that do not match a pattern. For example, to find all students with emails that do not end with "gmail.com", we could use the following query:

SQL
SELECT * FROM students
WHERE email NOT LIKE '%gmail.com';

Try it yourself

Given a table of customers, find all customers whose email contains @example.com.

Data Types and Limitations

The LIKE operator works with string data types and is typically used with varchar columns. It's important to note that using the LIKE operator with large datasets can have performance implications and is not the most optimal way to filter data. Depending on the query, the database's query planner can use indexes to optimize the query, but other times a full scan of the table is necessary. For searching over large datasets, some database engines such as PostgreSQL support extensions for full-text search that utilize tokenization to optimize searches over large amounts of text.

Next Lesson