Wildcards in MySQL

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 –

1. Using wildcard in MySQL can increase the performance of an application
2. It can reduce the time to filter the record from the database
3. Complex SQL queries can be converted into simple one using wildcards
4. 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.

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 –

1. MySQL Server
2. MySQL String functions
3. MySQL Operators
4. MySQL BETWEEN