SQL Wildcards

Wildcards are a useful technique when working with strings in SQL. Learn all about them and see some examples in this article.

What is an SQL Wildcard?

An SQL 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:

Character	Meaning
%	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 an SQL 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:

• Andrews
• Anderson
• Abraham
• An
• A
• Arton

They would all match because they begin with A and have any number of characters after them.

The following values would not match:

• Bandy
• Tea
• MICHAELS
• LARDER
• Smith
• Johnson

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. You can find the script to set up this table on my GitHub repository here.

ID	NAME	LIMIT
1	Smith	2000
2	Johnson	4000
3	Anderson	5000
4	Wallace	1500
5	Soren	800

 

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%';

Result:

ID	NAME	LIMIT
1	Smith	2000
5	Soren	800

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%';

Result:

ID	NAME	LIMIT
2	Johnson	4000
3	Anderson	5000
5	Soren	800

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';

Result:

ID	NAME	LIMIT
4	Wallace	1500

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 an SQL wildcard rather than the % symbol

SELECT id, name, limit
FROM customer
WHERE name LIKE '_o%';

Result:

ID	NAME	LIMIT
5	Soren	800

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%';

Result:

ID	NAME	LIMIT
1	Smith	2000
3	Anderson	5000
4	Wallace	1500
5	Soren	800

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 an SQL 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 SQL 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.

 

Conclusion

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.