Introduction to Wildcards in MySQL
An operator which performs the function of replacing in a string, zero to any number of characters, and is denoted by a concerned symbolic notation while being specified in the query, and is often used with the LIKE operator in the WHERE clause, so as to identify a particular arrangement of characters from all requisite values of a text field, and existing in two forms which are percentage and underscore are referred to as wildcard in SQL.
Features Of Wildcards
Some of the important features of Wildcards are given below –
- Using wildcard in MySQL can increase the performance of an application
- It can reduce the time to filter the record from the database
- Complex SQL queries can be converted into simple one using wildcards
- Using wildcards we can develop powerful search engines in a large data-driven application. Searching in the data-driven application are much more dependent on the use of wildcards
Type of Wildcards
Following are some type of wildcards that can be used in the SQL queries, It can be used individually or in the combination of other wildcards
1) % The Percentage Character
% Symbol character can be used either in searching or filtering the record. % can be used either in the first place, in the last or at both side of the string Such as
SELECT * FROM ‘items’ WHERE ‘item_description’ LIKE ‘%Motor%’;
In the above query, all those rows will be extracted from the database where column ‘item_description’ will contain the ‘Motor’ word in the middle of the description text.
SELECT * FROM ‘items’ WHERE ‘item_description’ LIKE ‘%Motor’;
In the above query all those rows will be extracted from the database where column ‘item_description’ will contain the ‘Motor’ word in the last of the description text.
4.5 (7,362 ratings)
View Course
SELECT * FROM ‘items’ WHERE ‘item_description’ LIKE ‘Motor%’;
In the above query all those rows will be extracted from the database where column ‘item_description’ will contain the ‘Motor’ word at the start of the description text.
Example #1 – A ‘items’ table containing the following record in the below given table
In the given table all those items needed which contains the name as ‘motor’. So the SQL query will use wildcard character “%” in searching 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
A character can be used as a wildcard character, when need to filter record from the database as a single character at any location may be any character in the string then this _ (underscore) wildcard can play an important role in the SQL query.
Example #1: In the below given `items` table, there are 4 items available When need to filter record which having first 4 characters are `AH00` & last 3 characters are `2EC` & only 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.
To find out the expected record from the database the query is given below
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';
This wildcard _ (underscore) can be used multiple times in the SQL query at any position depending on the requirement and also can be used in the combination of other wildcard characters.
3) – The Hyphen Character Wildcard
(-) Character can be used as a wildcard character when you need to filter record from the database as a character in a certain range at any location then this – (Hyphen) wildcard can be used efficiently in the SQL query.
Example #1 – In the above given `items` table, In a scenario there is a need of all those items whose name is starting from a to j. In this case queries will be like as given below
SELECT * FROM `items` WHERE `item_description` LIKE '[a-j]%';
4) [] The Square Bracket Wildcard
([]) Square bracket character can be used in the query to select all data that can have string as in C & I at the particular location;
Example #1 – In the above given `items` table, In a scenario there is a need of all those items whose name matching with starting character C & Ij. In this case queries will be like 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 is starting with character C & I.
SELECT * FROM `items` WHERE `item_description` LIKE '[^CI]%';
6) # Hash Wildcard
# The Hash character can be used in the query to select all those records that contain any numeric character at the place of # wildcard.
Conclusion
Wildcards used in all types of databases like MySQL, MS Access, Oracle. Wildcard work the same as regular expressions works. Multiple wildcards can be used at once while searching, filtering in database. All wildcards can be used in the SQL query either individually or in a combination of any other wildcards.
Recommended Articles
This is a guide to Wildcards in MySQL. Here we discuss the basic concept, features and types of wildcards along with some examples respectively. You may also look at the following articles to learn more –
