Wildcards are a useful technique when working with strings in SQL. Learn all about them and see some examples in this article.
What is a Wildcard?
A wildcard is a character in SQL that is used to substitute for one or more characters in a string.
If you don’t know the entire string you’re searching for, or searching for a partial match, the wildcard feature will be useful to you.
Wildcard Characters in SQL
The wildcard characters are part of the SQL standard, so they work in Oracle, SQL Server, MySQL, and PostgreSQL.
These characters are:
|%||Represents zero or more characters|
|_||Represents exactly one character|
You can use these characters inside a string to ask the database engine to substitute other characters.
How do you do that? You use the LIKE keyword.
The SQL LIKE Keyword
To use a wildcard character in your query, you’ll need to use the LIKE keyword. This is used in SQL as part of the WHERE clause to find all records that partially match the specified string, and your specified string has wildcards.
For example, if you wanted to find all customers where the first_name started with “A”, you could use this WHERE clause:
WHERE first_name LIKE 'A%'
This uses the LIKE keyword instead of the = sign, which we need to do because we’re using a partial match.
Inside the string, we start with the character “A” then a percentage sign “%”. This means for a record to be returned, the first_name must be equal to “A” and then any number of characters.
So, the following values would match:
They would all match because they begin with A and have any number of characters after them.
The following values would not match:
Even though some of them have an “A”, the character is not the first character, so it does not match the specified string.
Examples of SQL Wildcards
Here are some examples of using wildcards in SQL. These queries will use the sample data in the customer table here:
Example 1 – starts with character
This SQL example demonstrates how to use an SQL wildcard to find all records where the name starts with a specific character.
SELECT id, name, limit FROM customer WHERE name LIKE 'S%';
Notice that the LIKE keyword is followed by ‘S%’. This means that the first character must be an “S”, then any number of characters can follow. Smith and Soren are shown, but all others are not. Even though Johnson and Anderson have an “s” in their values, they are not at the beginning so are not returned.
Example 2 – SQL contains string
This example shows an SQL query that finds records where a text value contains another text value.
SELECT id, name, limit FROM customer WHERE name LIKE '%o%';
This query shows all records where the name contains “o”, because a % symbol is before and after the “o” and it represents any number of characters.
Example 3 – ends with character
This example shows how to find a record where the name ends with a specific character.
SELECT id, name, limit FROM customer WHERE name LIKE '%e';
The only row returned is Wallace, because that’s the only name that ends with “e”.
Example 4 – single character substitution
This example uses a single character as a wildcard rather than the % symbol
SELECT id, name, limit FROM customer WHERE name LIKE '_o%';
This result shows all records where there is a single character, then an “o”, then any number of characters. The only matching value is “Soren”.
Example 5 – not like
This example uses the NOT keyword along with the LIKE keyword.
SELECT id, name, limit FROM customer WHERE name NOT LIKE 'J%';
The record for “Johnson” is not shown as it matches the NOT LIKE criteria and is therefore not shown.
Example 6 – update
This example shows how you can use the UPDATE statement with a wildcard.
UPDATE customer SET limit = limit + 200 WHERE name LIKE '%n%';
This statement increases the limit for all customers that have the letter “n” in their name, which are Anderson, Johnson, Soren.
Example 7 – delete
This example shows how you can use the DELETE statement with a wildcard.
DELETE FROM customer WHERE name LIKE 'S%';
This statement will delete all customers that have a name beginning with S, which are Smith and Soren.
Can You Use Wildcards with Equals?
You cannot use wildcard characters with the equals sign. If you do, then the wildcard lookup feature won’t work and you’ll get incorrect results. It needs to be used with a LIKE keyword to perform a partial match.
Can You Use Wildcards with IN?
You cannot use wildcards with an IN keyword. This is because the IN keyword performs an exact match, and wildcards must work with a partial match.
Some ways you can perform LIKE on multiple matches are
Using multiple OR keywords:
WHERE first_name LIKE 'A%' OR first_name LIKE 'B%'
Or you can use a regular expression that matches your criteria. I’ve written more about regular expressions in SQL here.
The wildcard functionality in SQL applies to all vendors of SQL (e.g. Oracle, SQL Server, MySQL, PostgreSQL). It allows you to do partial matching on a string and is a useful feature of SQL.