How to Use a SQL Wildcard

SQL wildcard allows us to filter data matching certain patterns in SQL. We use SQL wildcards with the LIKE operator in the WHERE clause of a query to filter data. In this beginner’s article, we’ll look at everything you need to know about basic SQL wildcards.

If you like playing cards, then you know that wildcards can substitute any other card in your deck. Similarly, SQL wildcards can substitute one or more characters.

To start with our SQL wildcards, let’s look at a simple customers table for our pet store:

SQL Wildcard with the LIKE Operator

Imagine you witness a customer leaving a pet store without paying for his order. These things happen—he just forgot. You know that the seller in the pet store has a database of customers, and you ask him if he knows the name of the customer so you can extract the address and follow up with him. But the seller is not sure about the customer’s name. He knows that the name of the client ends with two letters, os, and that it was short, about only three letters. Maybe it was something like Tos, Los, Kos etc. Using the power of a SQL wildcard, you can handle one ambiguous letter in their database. To represent one character, we use the underscore SQL wildcard. Our SELECT query looks like this:

SELECT * FROM customers WHERE surname LIKE ‘_os’;

Our query now returns all of our surnames, and we can skip writing the statement for every word combination:

SELECT * FROM customers WHERE surname IN (‘Kos’,’Tos’,’Los’);

Note that the list of surnames above is by no means exhaustive because the underscore wildcard replaces any character—even one that’s not a letter!

Caution: Using SQL Wildcards Without LIKE Operator

SQL wildcards only work in the LIKE operator. If you place a wildcard inside an ordinary string that’s not an argument to the LIKE operator, you’ll find that SQL will treat that wildcard as a literal character appearing in the string. For example, consider this alternative query that doesn’t use the LIKE operator:

SELECT * FROM customers WHERE surname = ‘_os’;

This query would search all surnames that literally equal ‘_os’, and you can bet there are no such records in our table. This is a typical novice mistake, so be careful when using SQL wildcards.

Inverting SQL Wildcard Filters

To get the inverse of a SQL wildcard filter, such as to find all customers whose last names are not Los, Tos, Kos, etc., you simply apply a negation, NOT, to the LIKE operator:

SELECT * FROM customers WHERE surname NOT LIKE ‘_os’;

Using SQL Wildcards to Represent a Collection of Characters

The underscore wildcard is not the only one available in SQL. A more commonly used SQL wildcard is the percent sign (%), which is used to represent one or more characters. So if we’d like to list all customers who live in German cities that end in ‘burg’, we’d write the following query:

SELECT * FROM customers WHERE city LIKE ‘%burg’;

This query will return all records for customers whose city of residence is like Hamburg, Augsburg, Oldenburg, Duisburg, and others.

Just as we negated the underscore SQL wildcard, we can also negate the percentage SQL wildcard. So if we’d like to extract records for customers whose city of residence does not end in ‘burg’, we’d write:

SELECT * FROM customers WHERE city NOT LIKE ‘%burg’;

Note: Avoid using the percentage wildcard at the start of a string with the LIKE operator, if possible. This construction is very expensive, as the database needs to evaluate every combination of strings matching that ending pattern. The usage of % after some characters is less expensive in terms of computer resources, as the database already knows the space of strings that it needs to evaluate.

Combining Wildcards in LIKE Statements

It’s important to note that you can combine SQL wildcards. If you’d like to search for all customers whose city of residence starts with a ‘W’, ends in ‘burg’, and has at least one letter between the ‘W’ and ‘burg’, you can write:

SELECT * FROM customers WHERE city NOT LIKE ‘W_%burg’;

Go Wild with SQL Wildcards

In this article, we looked at how to use SQL wildcards to filter tables. We only covered the basics here. To learn more about SQL wildcards, I highly recommend that you learn about regular expressions. But be wary, because there’s a saying in computer science: If you try to solve a problem using regular expressions, you’ll end up with two problems. Regular expressions are an advanced topic, so have some experience under your belt before you try to master them.