How to use wildcards in SQL
Wildcards in SQL are special characters usually used with the LIKE operator in SQL queries to perform pattern matching within the textual data. They help us to search for data that matches a specific pattern rather than an exact value. Here are some of the most commonly used wildcards in SQL.
Percentage sign (
%) wildcardUnderscore (
_) wildcardSquare brackets (
[]) wildcardCaret (
^) wildcardEscape character
Note: All the sample data presented in the tables are used to test different widcards explained here.
Percentage sign (
%) wildcard: The percent sign%is a versatile wildcard character that matches zero, one, or multiple characters in a string. It’s often used when we want to find data that matches a specific pattern where some parts of the pattern can vary. In the following example, we’ll be using the followingemployeestable as our example:
CREATE TABLE employees(Id INT PRIMARY KEY,Name VARCHAR(30),email VARCHAR(30));
We have input the following data into our table:
Employees
ID | Name | |
01 | John | john@gmail.com |
02 | Zack M | zack@hotmail.com |
03 | Mark | mark@gmail.com |
We can use the following widget to practice all the possible scenarios with the percentage wildcard. Press the run button to view the result of the following queries.
-- Find all names starting with "J"SELECT * FROM employees WHERE name LIKE 'J%';-- Find all email addresses from a specific domainSELECT * FROM employees WHERE Email LIKE '%@gmail.com';
In the above example, on line 2 LIKE 'J%' matches all values starting with “J”followed by any number of characters. On line 5, LIKE '%gmail.com' matches all values ending with “@gmail.com.” It can find email addresses from a specific domain.
Underscore (
_) wildcard: The underscore_is used when we want to match a single character at a specific position in a string. It’s useful for more precise pattern matching. In the following example, we will be using the followingwordsandcontactstable as our example:
CREATE TABLE words(Id INT PRIMARY KEY,Word VARCHAR(30));CREATE TABLE contacts(User_id INT PRIMARY KEY,User_name VARCHAR(30),Phone_number VARCHAR(30));
We have input the following data into our tables:
Words
ID | Word |
01 | t h a t |
02 | this |
03 | hello |
Contacts
User_id | User_name | Phone_no |
01 | Andy | (+01) 123 456 789 |
02 | Andrew Z | (+02) 221 331 441 |
03 | Mark Weigh | (+03) 121 131 141 |
We can use the following widget to practice all the possible scenarios with the underscore wildcard. Press the run button to view the result of the following queries.
-- Find all words ending with "t"SELECT * FROM words WHERE Word LIKE '_ _ _ t';-- Find phone numbers with a specific area codeSELECT * FROM contacts WHERE phone_number LIKE '(___)%';
In the above example, on line 2 LIKE '_ _ _ t' returns word having three characters separated by a space and ending with a character t. On line 5, LIKE '(___)%' returns a number started with a specific area code in small brackets.
Square brackets ([]) wildcard: Square brackets allow us to specify a character range or a list of characters that can match a single character. It’s useful for more customized pattern matching. In the following example, we will be using the following
Wordstable as our example.
CREATE TABLE Words(Id INT PRIMARY KEY,Word VARCHAR(30));
We have input the following data into Words table:
Words
ID | Word |
01 | ABACUS |
02 | Apple |
03 | Basket |
04 | Cpple |
05 | Epple |
We can use the following widget to practice all the possible scenarios with the square bracket wildcard. Press the run button to view the result of the following queries.
-- Find words that start with character from "A" to "D"SELECT * FROM words WHERE word REGEXP '[A-D]pple';
In the above example, '[A-D]pple' matches values that start with any character in the range A-D, followed by “pple.” This would match “Apple” and “Cpple” but not “Epple.”
Caret (^) wildcard: The caret
^can be used to specify characters or ranges to exclude from matching. In the following example, we’re using the tablewordsthat is used in the previous example of square bracket wildcard. We can use the following widget to practice all the possible scenarios with the caret wildcard. Press the run button to view the result of the following queries.
-- Find words that do not start with a vowelSELECT * FROM words WHERE word REGEXP '^[^AEIOU]';
In the above example, ^[^AEIOU] is used to find all the words that are not starting with a vowel character. Here, we’re using REGEXP instead of like keyword as it is supported here. But in some other versions of SQL, we can use caret wildcard with the like keyword as well.
Note: Please note that the exact syntax for achieving this result might vary depending on your specific database system. If you are using a database other than SQL, you might need to consult your database’s documentation.
Escape character: Sometimes, we may want to search for the wildcard character as a literal character. In such cases, we can use the escape character (usually backslash
\) to escape the wildcard character. In the following example, we will be using the followingdatatable as our example.
CREATE TABLE data(Id INT PRIMARY KEY,Name VARCHAR(30));
We have input the following data into the data table:
Data
ID | Name |
1 | Michael |
2 | Andy |
3 | Andrew% |
We can use the following widget to practice all the possible scenarios with the escape character. Press the run button to view the result of the following query.
-- Find all names containing the percent sign (%)SELECT * FROM data WHERE name LIKE '%\%%';
In the above example, on line 2 LIKE '%\%%' returns all the names having a % symbol.
These wildcards are powerful tools for searching and filtering data in SQL. Remember that wildcard usage might vary slightly between different database systems, so always refer to the documentation of the specific database before using it for precise wildcard syntax and behavior.
