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:

SNWildcard SymbolDescriptions
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:

MySQL Wildcards

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:

MySQL Wildcards

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:

MySQL Wildcards

This statement returns all employees whose X value can be in any position:

It will give the output as below:

MySQL Wildcards

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.

MySQL Wildcards

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:

MySQL Wildcards

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:

MySQL Wildcards

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:

MySQL Wildcards

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:

MySQL Wildcards

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:

MySQL Wildcards

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:

MySQL Wildcards

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

MySQL Wildcards
Next TopicMySQL Alias