Updated May 10, 2023
Introduction to Wildcards in MySQL
In SQL, a wildcard is an operator that replaces zero to any number of characters in a string. To identify a particular arrangement of characters from all required values of a text field, you can use a specific symbol to denote the wildcard operator, and use it with the LIKE operator in the WHERE clause. There are two forms of wildcards, namely percentage and underscore.
Features of Wildcards
Here are some important features of Wildcards in SQL:
- Using wildcards in MySQL can increase the performance of an application.
- It can reduce the time to filter the record from the database.
- Wildcards can simplify complex SQL queries.
- We can develop powerful search engines in a large data-driven application using wildcards. Searching in the data-driven application is much more dependent on the use of wildcards.
Type of Wildcards
Following are some types of wildcards that can be used in SQL queries. You can use wildcards individually or in combination with other wildcards in SQL queries.
1) % The Percentage Character
% Symbol characters can be used either in searching or filtering the record. % can be used either in the first place, in the last, or on both sides of the string, Such as
SELECT * FROM 'items' WHERE 'item_description' LIKE '%Motor%';The above query extracts all rows from the database where the column ‘item_description’ contains the word ‘Motor’ in the middle of the description text.
SELECT * FROM 'items' WHERE 'item_description' LIKE '%Motor';The above query extracts all rows from the database where the column ‘item_description’ contains the word ‘Motor’ at the end of the description text.
SELECT * FROM 'items' WHERE 'item_description' LIKE 'Motor%';The above query extracts all rows from the database where the column ‘item_description’ contains the word ‘Motor’ at the beginning of the description text.
Example – A ‘items’ table containing the following record in the below-given table
We need all items in the given table that contain the name ‘motor’. So the SQL query will use the wildcard character “%” in searching for the needed data from the database.
SELECT * FROM 'items' WHERE 'item_description' LIKE %Motor%;
After execution of the SQL query, above-given record will be available.
2) _ The Underscore Character
In SQL queries, the underscore (_) wildcard can be used to filter records based on a single character at any location in a string. This wildcard character can be helpful when any character may appear at that position.
Example: In the below given `items` table, there are four items available When needing to filter the record, which having first four characters are `AH00` & the last three characters are `2EC` & only the 5th position character may be anything, in this type of scenario wildcard character _ will be needed at the 5th position in the SQL query to filter the record from the database.
Below is the query to find the expected record from the database.
SELECT * FROM 'items' WHERE 'item_code' LIKE 'AH00_2EC';
In the below-given query, using _ (underscore) in the combination of 2. Here
SELECT * FROM 'items' WHERE 'item_code' LIKE 'AH00__EC';
Depending on the requirement, this wildcard _ (underscore) can be used multiple times in the SQL query at any position. Also, it can be used in combination with other wildcard characters.
3) – The Hyphen Character Wildcard
In SQL queries, you can use the hyphen (-) character as a wildcard to filter records containing a character within a certain range at any position. This can be a useful technique for efficiently searching and filtering the database.
Example – In the above given `items` table, there is a need for all those items whose names start from a to j. In this case, queries will be as given below
SELECT * FROM 'items' WHERE 'item_description' LIKE '[a-j]%';4) [] The Square Bracket Wildcard
([]) Square bracket characters can be used in the query to select all data that can have a string, as in C & I at the particular location;
Example – In the above given `items` table, there is a need for all those items whose name matches with starting characters C & Ij. In this case, queries will be as given below
SELECT * FROM 'items' WHERE 'item_description' LIKE '[CI]%';5) ^ The Caret Wildcard
^ The Caret character can be used in the query to de-select all those records that start with characters C & I.
SELECT * FROM 'items' WHERE 'item_description' LIKE '[^CI]%';6) # Hash Wildcard
You can use the Hash character (#) in a query to select all records that contain any numeric character in place of the # wildcard.
Conclusion – Wildcards in MySQL
Wildcards used in all types of databases like MySQL, MS Access, Oracle. Wildcard work the same as regular expressions works. In the SQL query, you can use multiple wildcards simultaneously to search and filter the database. You can use each wildcard individually or combine them with other wildcards.
Recommended Articles
We hope that this EDUCBA information on “Wildcards in MySQL” was beneficial to you. You can view EDUCBA’s recommended articles for more information.
