Wildcards

Beginner

Sometimes, you’ll want to filter by text fields (also known as string fields). For example, you might want to retrieve all students whose last_name begins with “J”, or find all tests where the code begins with the string “GEOG”.

This process of filtering by (partial) string matches is known as pattern matching and, while it’s possible to do pattern matching using the operators covered in previous tutorials, it’s usually much easier to use wildcards.

In SQL, wildcards are special characters that allow you to perform pattern matching in string values. The most commonly used wildcards in SQL are:

  1. % (Percent Sign)
    • Represents zero, one, or multiple characters.
  2. _ (Underscore)
    • Represents a single character.

If that sounds a little confusing, don’t worry: it will all make sense!

Wildcards are used in conjunction with the LIKE operator to search for a specified pattern in a column's value.

For example, to search for all students whose last_name begins with “J”, we can write the following query:

SELECT *
FROM students
WHERE last_name LIKE 'J%'

This query will retrieve all columns from the students table where the last_name column starts with the letter 'J', followed by zero, one, or multiple characters. Here’s the result:

In this example, the “%” wildcard acted as shorthand for “an unspecified number of number of characters”. When we wrote LIKE 'J%', we were using SQL to tell the RDBMS to fetch “all rows from the students table where the last name began with ‘J’ and was followed by any number of characters”.

The “_” wildcard works in a similar way. Here’s a query which uses it:

SELECT *
FROM students
WHERE first_name LIKE 'J_m';

This query will retrieve all rows from the students table where the first_name starts with the character 'J', followed by any single character, and then followed by the character 'm'.

Using multiple wildcards

It’s also possible to use multiple wildcards in the same pattern. The query below, for example, uses to “%” wildcards to fetch all students whose first_name contains an “a”: 

SELECT *
FROM students
WHERE first_name LIKE '%a%';

This query will retrieve all columns from the students table where the first_name contains the character 'a' anywhere in the string, surrounded by zero, one, or multiple characters.

Practice

Now it's your turn! Open the following questions in a new tab to have a go at using wildcards:

Previous Next