MySQL Wildcards
The wildcards in MySQL are characters that allow us to search complex data from the table very easily and quickly. It works with string by substituting one or more characters and produce the result after matching the string into the table.
In normal comparison, we use two string where each character of both strings should be matched exactly before giving the output. While in wildcards, they provide flexibility to use a single character or group of characters in a string that will be acceptable for another string.
MySQL uses wildcards in conjunction with the LIKE or NOT LIKE comparison operators. The LIKE operator works with the WHERE clause to find the result for a specified pattern in a column.
Advantages of Wildcards
MySQL has the following advantages of wildcards:
- It improves the performance of an application.
- It saves time in filtering records from the table.
- It makes the use of complex queries into simple ones very easily and quickly.
- It allows us to develop powerful search engines into the large data-driven application.
Types of Wildcards
Following are the common types of wildcards used in MySQL that can be used individually or a combination of wildcards:
| SN | Wildcard Symbol | Descriptions | |
|---|---|---|---|
| 1. | % | This symbol indicates zero or more characters. | |
| 2. | _ | This symbol is used to match exactly one(single) character. |
Now, we are going to understand the use of these wildcards symbols in MySQL with the following examples:
Let us first create a table named “employee” that contains the following data:
1. The Percent symbol(%)
It is used to search or filter the records from the table that specifies 0, 1, or more characters. We can use it either in the first place, last place, or both sides of a string with the LIKE clause.
Syntax
The basic syntax for using this wildcard character is:
In the above syntax, the ‘X‘ specifies any single character, and % matches any number of characters.
Example
This statement returns all employees whose city name starts with ‘F’ character.
It will give the following output:
Again, this statement returns all employees from the table whose city name starts with ‘a’ and ends with ‘a’ character:
After the successful execution, we will get the result below:
This statement returns all employees whose X value can be in any position:
It will give the output as below:
If we want to returns all employees from the table whose city does not start with ‘a’ and ends with ‘a’ character, execute the below statement:
We will get the below result where we can see that there is no city name starts with ‘a’ and ends with ‘a’ character.
2. Underscore symbol(_)
We can use it when there is a need to returns the result from a table that matches exactly a single character from any location.
Syntax
The basic syntax for using this wildcard character is:
In the above syntax, the ‘X‘ specifies any string pattern, and _ symbol matches exactly one character.
Example
This statement returns all employees whose age belongs to 40 to 49:
We will see the result as below:
This statement returns all the employees from the table whose city name starts with any character followed by ‘lorida’ character:
After the successful execution, we will get the result below:
If we want to return all the employees from the table whose city does not start with any character followed by ‘lorida’ characters, execute the below statement:
The following output appears:
Combined Wildcards
We have learned that wildcard can also be used as a combination of both, let us understand it with the example below.
This statement returns all the employees whose name starts with X and have at least two characters in length:
We will get the output below:
It is another example that produces the output where all employees who have ‘5‘ at their second position in income column:
We can get the below output:
3. The Hyphen symbol(-)
This symbol is used to return the result when we need to filter the record from the table in a certain range. It is an extended version of wildcard characters that use the REGEXP_LIKE() function.
This statement fetches all records from employees table whose city containing letters ‘b’, ‘c’, or ‘d’ as shown below:
Output
After the successful execution, we will get the result below:
If we want to return all the employees from the table whose city does not contain letters ‘b’, ‘c’, or ‘d’ as shown below:
Output
