How To Use Wildcards in SQL

Introduction

Like many computer languages, SQL allows the use of various wildcard characters. Wildcards are special placeholder characters that can represent one or more other characters or values. This is a convenient feature in SQL, as it allows you to search your database for your data without knowing the exact values held within it.

This guide will go over how to query data using SQL’s designated wildcards.

Prerequisites

In order to follow this guide, you will need a computer running some type of relational database management system (RDBMS) that uses SQL. The instructions and examples in this guide were validated using the following environment:

• A server running Ubuntu 20.04, with a non-root user with administrative privileges and a firewall configured with UFW, as described in our initial server setup guide for Ubuntu 20.04.
• MySQL installed and secured on the server, as outlined in How To Install MySQL on Ubuntu 20.04. This guide was verified with a non-root MySQL user, created using the process described in Step 3.

You’ll also need a database and table loaded with some sample data which you can practice using with wildcards. If you don’t have this, you can read the following Connecting to MySQL and Setting up a Sample Database section for details on how to create a database and table which this guide will use in examples throughout.

Connecting to MySQL and Setting up a Sample Database

If your SQL database system runs on a remote server, SSH into your server from your local machine:

ssh sammy@your_server_ip

Then open up the MySQL server prompt, replacing sammy with the name of your MySQL user account:

mysql -u sammy -p

Create a database named wildcardsDB:

CREATE DATABASE wildcardsDB;

If the database was created successfully, you’ll receive output like this:

Output
Query OK, 1 row affected (0.01 sec)

To select the wildcardsDB database, run the following USE statement:

USE wildcardsDB;

Output
Database changed

After selecting the database, you can create a table within it with the following command. As an example, let’s say you wanted to create a table named user_profiles to hold profile information of users of an application. This table will hold the following five columns:

• user_id: this column will hold values of the int data type that. It will also serve as the table’s primary key, with each value functioning as a unique identifier for its respective row
• name: each user’s name, expressed using the varchar data type with a maximum of 30 characters
• email: this column will hold users’ email addresses, also expressed using the varchar data type but with a maximum of 40 characters
• birthdate: using the date data type, this column will hold each user’s date of birth
• quote: each user’s favorite quote. To provide an adequate number of characters for quotes, this column also uses the varchar data type, but with a maximum of 300 characters

Run the following command to create this sample table:

CREATE TABLE user_profiles (
user_id int,
name varchar(30),
email varchar(40),
birthdate date,
quote varchar(300),
PRIMARY KEY (user_id)
);

Output
Database changed

Then insert some sample data into the empty table:

INSERT INTO user_profiles
VALUES
(1, 'Kim', 'bd_eyes@example.com', '1945-07-20', '"Never let the fear of striking out keep you from playing the game." -Babe Ruth'),
(2, 'Ann', 'cantstandrain@example.com', '1947-04-27', '"The future belongs to those who believe in the beauty of their dreams." -Eleanor Roosevelt'),
(3, 'Phoebe', 'poetry_man@example.com', '1950-07-17', '"100% of the people who give 110% do not understand math." -Demitri Martin'),
(4, 'Jim', 'u_f_o@example.com', '1940-08-13', '"Whoever is happy will make others happy too." -Anne Frank'), 
(5, 'Timi', 'big_voice@example.com', '1940-08-04', '"It is better to fail in originality than to succeed in imitation." -Herman Melville'),
(6, 'Taeko', 'sunshower@example.com', '1953-11-28', '"You miss 100% of the shots you don\'t take." -Wayne Gretzky'),
(7, 'Irma', 'soulqueen_NOLA@example.com', '1941-02-18', '"You have brains in your head. You have feet in your shoes. You can steer yourself any direction you choose." -Dr. Seuss'), 
(8, 'Iris', 'our_town@example.com', '1961-01-05', '"You will face many defeats in life, but never let yourself be defeated." -Maya Angelou');

Output
Query OK, 8 rows affected (0.00 sec)
Records: 8  Duplicates: 0  Warnings: 0

With that, you’re ready to follow the rest of the guide and begin learning about how to use wildcards to query data in SQL.

Querying Data with Wildcards

As mentioned in the Introduction, wildcards are special placeholder characters that can represent one or more other characters or values.

In SQL, there are only two defined wildcard characters:

• _: When used as a wildcard, an underscore represents a single character. For example, s_mmy would match sammy, sbmmy, or sxmmy.
• %: The percentage sign wildcard represents zero or more characters. For example, s%mmy would match sammy, saaaaaammy, or smmy.

These wildcards are used exclusively in a query’s WHERE clause with either the LIKE or NOT LIKE operators.

To illustrate using the sample data from the Prerequisites section, let’s say you know at least one of the users listed in the user_profiles table has a name that’s three letters long and ends with “im,” but you aren’t sure who they are. Because you’re only unsure about the first letter in these users’ names, you could run the following query, which uses the _ wildcard, to find who they are:

SELECT * FROM user_profiles WHERE name LIKE '_im';

Output
+---------+------+---------------------+------------+---------------------------------------------------------------------------------+
| user_id | name | email               | birthdate  | quote                                                                           |
+---------+------+---------------------+------------+---------------------------------------------------------------------------------+
|       1 | Kim  | bd_eyes@example.com | 1945-07-20 | "Never let the fear of striking out keep you from playing the game." -Babe Ruth |
|       4 | Jim  | u_f_o@example.com   | 1940-08-13 | "Whoever is happy will make others happy too." -Anne Frank                      |
+---------+------+---------------------+------------+---------------------------------------------------------------------------------+
2 rows in set (0.00 sec)

The NOT LIKE operator has the opposite effect of LIKE. Rather than returning every record that matches the wildcard pattern, it will return every row that doesn’t match the pattern. To illustrate, run the previous query again but replace LIKE with NOT LIKE:

SELECT * FROM user_profiles WHERE name NOT LIKE '_im';

This time, every row where the value in the name column matches _im is omitted from the result set:

Output
+---------+--------+----------------------------+------------+--------------------------------------------------------------------------------------------------------------------------+
| user_id | name   | email                      | birthdate  | quote                                                                                                                    |
+---------+--------+----------------------------+------------+--------------------------------------------------------------------------------------------------------------------------+
|       2 | Ann    | cantstandrain@example.com  | 1947-04-27 | "The future belongs to those who believe in the beauty of their dreams." -Eleanor Roosevelt                              |
|       3 | Phoebe | poetry_man@example.com     | 1950-07-17 | "100% of the people who give 110% do not understand math." -Demitri Martin                                               |
|       5 | Timi   | big_voice@example.com      | 1940-08-04 | "It is better to fail in originality than to succeed in imitation." -Herman Melville                                     |
|       6 | Taeko  | sunshower@example.com      | 1953-11-28 | "You miss 100% of the shots you don't take." -Wayne Gretzky                                                              |
|       7 | Irma   | soulqueen_NOLA@example.com | 1941-02-18 | "You have brains in your head. You have feet in your shoes. You can steer yourself any direction you choose." -Dr. Seuss |
|       8 | Iris   | our_town@example.com       | 1961-01-05 | "You will face many defeats in life, but never let yourself be defeated." -Maya Angelou                                  |
+---------+--------+----------------------------+------------+--------------------------------------------------------------------------------------------------------------------------+
6 rows in set (0.00 sec)

As another example, let’s say you know several of the users listed in the database have names that start with “I,” but you can’t remember all of them. You could use the % wildcard to list all of them, as shown with the following query:

SELECT user_id, name, email FROM user_profiles WHERE name LIKE 'I%';

Output
+---------+------+----------------------------+
| user_id | name | email                      |
+---------+------+----------------------------+
|       7 | Irma | soulqueen_NOLA@example.com |
|       8 | Iris | our_town@example.com       |
+---------+------+----------------------------+
2 rows in set (0.00 sec)

Note that in MySQL, by default, the LIKE and NOT LIKE operators are not case sensitive. This means the previous query will return the same results even if you don’t capitalize the “I” in the wildcard pattern:

SELECT user_id, name, email FROM user_profiles WHERE name LIKE 'i%';

Output
+---------+------+----------------------------+
| user_id | name | email                      |
+---------+------+----------------------------+
|       7 | Irma | soulqueen_NOLA@example.com |
|       8 | Iris | our_town@example.com       |
+---------+------+----------------------------+
2 rows in set (0.00 sec)

Be aware that wildcards are different from regular expressions. Generally, a wildcard refers to a character used in glob-style pattern matching, while regular expressions rely on a regular language to match string patterns.

Escaping Wildcard Characters

There may be times when you want to search for data entries that contain one of SQL’s wildcard characters. In such cases, you can use an escape character which will instruct SQL to ignore the wildcard function of either % or _ and instead interpret them as plain text.

For example, let’s say you know that at least a couple of the users listed in the database have a favorite quote that includes a percentage sign, but you aren’t sure who they are.

You could try running the following query:

SELECT user_id, name, quote FROM user_profiles WHERE quote LIKE '%';

However, this query won’t be very helpful. Because the percentage sign serves as a stand-in for any string of characters of any length, it will return every row in the table:

Output
+---------+--------+--------------------------------------------------------------------------------------------------------------------------+
| user_id | name   | quote                                                                                                                    |
+---------+--------+--------------------------------------------------------------------------------------------------------------------------+
|       1 | Kim    | "Never let the fear of striking out keep you from playing the game." -Babe Ruth                                          |
|       2 | Ann    | "The future belongs to those who believe in the beauty of their dreams." -Eleanor Roosevelt                              |
|       3 | Phoebe | "100% of the people who give 110% do not understand math." -Demitri Martin                                               |
|       4 | Jim    | "Whoever is happy will make others happy too." -Anne Frank                                                               |
|       5 | Timi   | "It is better to fail in originality than to succeed in imitation." -Herman Melville                                     |
|       6 | Taeko  | "You miss 100% of the shots you don't take." -Wayne Gretzky                                                              |
|       7 | Irma   | "You have brains in your head. You have feet in your shoes. You can steer yourself any direction you choose." -Dr. Seuss |
|       8 | Iris   | "You will face many defeats in life, but never let yourself be defeated." -Maya Angelou                                  |
+---------+--------+--------------------------------------------------------------------------------------------------------------------------+
8 rows in set (0.00 sec)

To escape the percentage sign, you can precede it with a backslash (\), MySQL’s default escape character:

SELECT * FROM user_profiles WHERE quote LIKE '\%';

However, this query won’t be helpful either, since it specifies that the contents of the quote column should only consist of a percentage sign. Hence, the result set will be empty:

Output
Empty set (0.00 sec)

To correct this, you’d need to include percentage sign wildcards at the beginning and end of the search pattern following the LIKE operator:

SELECT user_id, name, quote FROM user_profiles WHERE quote LIKE '%\%%';

Output
+---------+--------+----------------------------------------------------------------------------+
| user_id | name   | quote                                                                      |
+---------+--------+----------------------------------------------------------------------------+
|       3 | Phoebe | "100% of the people who give 110% do not understand math." -Demitri Martin |
|       6 | Taeko  | "You miss 100% of the shots you don't take." -Wayne Gretzky                |
+---------+--------+----------------------------------------------------------------------------+
2 rows in set (0.00 sec)

In this query, the backslash only escapes the second percentage sign, while the first and third ones are still acting as wildcards. Thus, this query will return every row whose quote column includes at least one percentage sign.

Note that you can also define custom escape characters with the ESCAPE clause, as in the following example:

SELECT user_id, name, email FROM user_profiles WHERE email LIKE '%@_%' ESCAPE '@';

Output
+---------+--------+----------------------------+
| user_id | name   | email                      |
+---------+--------+----------------------------+
|       1 | Kim    | bd_eyes@example.com        |
|       3 | Phoebe | poetry_man@example.com     |
|       4 | Jim    | u_f_o@example.com          |
|       5 | Timi   | big_voice@example.com      |
|       7 | Irma   | soulqueen_NOLA@example.com |
+---------+--------+----------------------------+
5 rows in set (0.00 sec)

This query defines the @ sign as an escape character, and returns every row whose email column contains at least one underscore. If you were to remove the ESCAPE clause, the query would return every row from the table, since each of them contain at least one @ sign.

Conclusion

By reading this guide, you learned how to use and escape wildcards SQL-based databases. The commands outlined here should work on any database management system that uses SQL. Keep in mind that every SQL database uses its own unique implementation of the language, so you should consult your DBMS’s official documentation for a more complete description of each command and their full sets of options.

If you’d like to learn more about working with SQL, we encourage you to check out the other tutorials in this series on How To Use SQL.