When working with MySQL databases, it’s often necessary to search for specific data patterns within a text string. This is where wildcard characters come in handy. In MySQL, there are two common wildcard characters that can be used with the LIKE operator, the first one is the percent sign (%), and the other one is the underscore sign (_). These characters allow you to match one or more characters in a text string, even if you don’t know the exact value.
This guide will explain the detailed information on how to use the wildcard character in MySQL.
How to Use Wildcard Characters in MySQL?
By using wildcard characters, you can search for specific patterns within a text string in MySQL. There are two most common wildcard characters in MySQL whose working/purpose and usage will be explored with the following examples.
Example 1: Matching Strings That Start With a Specific Pattern
To match the string that starts with a specific pattern using a wildcard character, the given below command can be utilized:
In the above example, the percent (%) sign (wildcard character) is utilized to match the particular pattern of the “customers_data” table. Here, the percent sign (%) is utilized to match any string of characters including zero characters.
Output
The output showed the customer’s last name that starts with the letter “D”.
Example 2: Matching Strings With a Specific Character Anywhere
To match the string with a specific character anywhere in the string data, the double percent (%..%) sign can be utilized. The example is given below:
In the above example, the character “S” is searched in the “last_name” column.
Output
The output depicts that the rows that have the character “S” anywhere in their “last_name” column have been filtered.
Example 3: Matching Strings That Contain a Specific Substring
To match the string with a specific Substring in the string data, the double percent (%..%) sign can be utilized. An example of searching the “Doe” in the “last_name” column of the “customers_data” table is given below:
Output
The output showed only those rows that have “Doe” in the “last_name” column.
Example 4: Matching Strings of a Specific Length
To match the string with a specific length, the underscore (_) sign can be utilized. Below is an example of searching for any character after the substring “Do” in the “last_name” column of the “customers_data” table:
Output
The output displayed the filtered data according to the pattern.
Example 5: Matching Strings With a Specific Character in a Specific Position
To match the string with a specific character in a specific position of the string, both the underscore (_) and the percent (%) sign can be utilized. Here is an example of searching the word “def” anywhere but after the first 8 characters in the “password” column of the “users_detail” table:
Output
The output showed the data according to the specific filter.
Conclusion
The wildcard characters in MySQL can be utilized to search for specific patterns within a text string. The two most common wildcard characters are the percent sign (%) and the underscore sign (_), used for different purposes. By using the “LIKE” operator with the “SELECT” statement, you can filter data in MySQL databases based on specific patterns and criteria. This post presented detailed information on the use of wildcard characters in MySQL.
