Which operator do you use in the where clause when you want to include a wildcard?
IntroductionLike 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. Show This guide will go over how to query data using SQL’s designated wildcards. PrerequisitesIn 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:
Note: Please note that many RDBMSs use their own unique implementations of SQL. Although the commands outlined in this tutorial will work on most RDBMSs, the exact syntax or output may differ if you test them on a system other than MySQL. 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 DatabaseIf your SQL database system runs on a remote server, SSH into your server from your local machine:
Then open up the MySQL server prompt, replacing
Create a database named
If the database was created successfully, you’ll receive output like this:
To select the
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
Run the following command to create this sample table:
Then insert some sample data into the empty table:
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 WildcardsAs 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:
These wildcards are used exclusively in a query’s To illustrate using the sample data from the Prerequisites section, let’s say you know at least one of the users listed in the
Note: In this example, an asterisk ( In certain applications and programming languages, and even some implementations of SQL, asterisks are used as wildcard characters that represent zero or more characters, just like the percentage sign used
in this example. However, the asterisk in the example above is not a wildcard, since it represents something specific — namely, every column in the The
This
time, every row where the value in the
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
Note that in MySQL, by default, the
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 CharactersThere 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 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:
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:
To escape the percentage sign, you can precede it with a backslash (
However, this query won’t be helpful either, since it specifies that the contents of the
To correct this, you’d need to include percentage sign wildcards at the beginning and end of the search pattern following the
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 Note that you can also define custom escape characters with the
This query defines the ConclusionBy 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. Is * a wildcard in SQL?To broaden the selections of a structured query language (SQL-SELECT) statement, two wildcard characters, the percent sign (%) and the underscore (_), can be used. The percent sign is analogous to the asterisk (*) wildcard character used with MS-DOS.
What are the 2 wildcards you can use with a like operator?There are two wildcards often used in conjunction with the LIKE operator: The percent sign (%) represents zero, one, or multiple characters. The underscore sign (_) represents one, single character.
Which can be used with wildcard characters?Examples of wildcard characters. What is the symbol for a wildcard in SQL?Overview of the SQL LIKE Operator. |