MySQL provides these two wildcards for constructing patterns.
- The percentage ‘%’
- The underscore ‘_’
Open your newly installed command-line client shell of MySQL and type your MySQL password to work on it.
We have created a new table called ‘teacher’ in our database having different records in it, as shown below.
MySQL LIKE with Percentage % Wildcard:
Percentage sign works differently while using at different locations with alphabets. In the first example, the percentage sign has been used at the last location of the pattern to fetch a record of two columns, ‘TeachName’ and ‘subject’, where the subject name starts with ‘C’. Upon trying the below LIKE query, we have got the below result.
Use of the percentage sign before the pattern means that the pattern will match the last location of a value. So we have been looking for the records of columns ‘TeachName’ and ‘subject’ where the teacher name containing the alphabet ‘a’ at the last location. We have found the below output.
If you want to search the string pattern at the middle of the value, you have to place the percentage sign at both the start and end of the pattern. We have searched for the ‘am’ pattern lies between teachers’ names using the below-stated query.
Use the percentage sign in the middle of the pattern to search for a matching value without knowing what comes in the middle of it. We have displayed all the data related to the teacher name starting with ‘S’ and ends with ‘a’.
MySQL LIKE with Underscore ‘_’ Wildcard:
We will be using a new table called ‘record’ for understanding the underscore wildcard operator. The wildcard underscore ‘_’ works as one character when placed at some location; that’s why it couldn’t work for more than two characters as a percentage operator does.
Let’s fetch matching values while placing the underscore at the last of the pattern location. You have to define the exact number of characters in that particular name. Otherwise, your query won’t work. We want to display the records of the ‘Name’ starts with ‘Za’, where the three underscores mean that the last three characters of this name can be anything, and the name should consist of only 5 characters.
Place the underscore operator at the start of the pattern to search for the value. This implies that the starting characters of a string value can be anything. After the specified character has been used, the percentage sign means that the string value can be of any length. So, when we execute this query, it will return the values with different lengths.
In the below query, we have been using the wildcard underscore in the middle of the pattern. This means that the character before the last alphabet can be anything but the last alphabet must be ‘a’. The percentage sign shows that the string can be of any length.
We will be using the underscore at the start and any point of the pattern while searching in the column ‘Country’. This shows that the second character of a pattern must be ‘u’.
MySQL LIKE with NOT Operator:
MySQL helps you merge the NOT operator with the LIKE operator to identify a string that doesn’t even match a particular sequence. We have been searching for the records of columns: ‘Name’, ‘City’ and ‘Country’, where the Country name must have the ‘i’ alphabet in its string at any of the middle locations. We have got three results for this particular query.
MySQL LIKE with Escape Characters:
The sequence you would like to match often includes wildcard characters, e.g., %10, 20, etc. In this scenario, we could use the ESCAPE clause to define an escape symbol such that the wildcard symbol is treated as a literal character by MySQL. When you do not specifically mention an escape character, the standard escape operator is backslash ‘\’. Let’s search for the values in the table having ‘_20’ at the end of city names. You have to add ‘\’ as a wildcard escape character before the ‘_20’ because the underscore is itself a wildcard. It shows the data of cities having ‘_20’ at the end of their names. The percentage sign means the start of the name can be of any length and can have any character.
In the below example, the ‘%’ sign at start and end is used as a wildcard operator as used before. The second last ‘%’ sign is a pattern to be searched, and ‘\’ is an escape character here.
The ‘%’ sign is the wildcard operator, ‘\’ is the escape character and the last ‘/’ sign is a pattern to be searched at the last position of names here.
Conclusion:
We have done with Like Clause & Wildcards, which are important instruments that help hunt for information that matches complicated patterns. I hope this guide has helped you reach your actual goal of learning LIKE operators and wildcard operators.
