SQL Wildcard Search

Wildcard characters are special characters that act as placeholders to represent one or more values. They are instrumental when searching for specific patterns in text or string types.

In SQL, wildcards play an essential role as they enable you to perform a “fuzzy” search for matching values without knowing the exact value.

In this article, we will explore various wildcard characters in Standard SQL. Keep in mind that the implementation may vary depending on your database engine.

Standard SQL Wildcard Characters

There are two types of wildcard characters supported in standard SQL:

Character	Description
% (percent sign)	Match zero or more characters
_ (underscore sign)	Match any single character

NOTE: Although all database engines support the previously mentioned wildcards, MS Access uses an asterisk (*) to represent zero or more characters.

Using SQL Wildcard Operators

We use the LIKE clause to search for matching patterns using wildcards. In some instances, you may find the ILIKE clause, a case-insensitive version of the LIKE operator.

Example Usage I

For example, to search for all the users whose names start with Aa, we can perform a query as shown below:

select column_name from table_name where name like ‘%Aa’;

The previous query should search the specified table for all matching records whose name starts with Aa.

An example result is shown below:

Aabid
Aadeep
Aadesh
Aadam
Aaron

You can also use the ILIKE operator, which will match the search without regard to the casing.

SELECT column_name FROM TABLE_NAME WHERE name ilike '%Aa';
-- Aabid
-- Aadeep
-- Aadesh
-- Aadam
-- Aaron

NOTE: All database engines may not support the ILIKE operator.

You can also search if a string contains a specific substring by running the query as:

SELECT column_name(s) FROM TABLE_NAME WHERE name LIKE '%sun%';

In the previous syntax, SQL will return all the strings that contain the substring sun.

NOTE: Use the previous feature with caution. It may incur significant performance penalties on your database, especially on a large data set.

To search if a string ends with a specific character or substring, run the following command:

SELECT column_name(s) FROM TABLE_NAME WHERE name LIKE '%.com';

The previous command should return all the matching strings that end with .com

Example Usage II

In some cases, you may want to search for one matching character. In this case, you can use an underscore wildcard as follows:

SELECT column_name(s) FROM TABLE_NAME WHERE name LIKE '_e%r%';

The previous example query should search for matches with a single start character.

Here’s an example result:

peter

SQL Negation

Although the LIKE and ILIKE operator are great for searching matching records, you may want to work with records that do not match the specified wildcard search.

For that, you can negate your result by setting the condition as NOT LIKE or NOT ILIKE as shown below:

SELECT column_name FROM TABLE_NAME WHERE name NOT LIKE '%Aa';

In this case, the previous command should return all other results except the one that starts with Aa.

Conclusion

This article explored using and working with wildcard operators in Standard SQL. As mentioned, some database engines may implement more or fewer wildcard characters. Check the documentation for your engine to learn more. We hope you found this article helpful. Check the other Linux Hint articles for more tips and tutorials.