MYSQL Find Matching Records with LIKE

The MySQL LIKE operator tests if a particular character string resembles the pattern mentioned. We will match a portion of the overall data present in a segment that doesn’t need to match precisely. We will cup tie our keyword with the sequence of the information available in columns by using wildcard query in various combinations. MySQL Wildcards are symbols that help match difficult criteria with search results and have been used in combination with a compare operator called LIKE or a contrast operator called NOT LIKE.

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.

>> SELECT * FROM data.teacher;

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.

>> SELECT TeachName, subject FROM data.teacher WHERE subject LIKE ‘C%’;

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.

>> SELECT TeachName, subject FROM data.teacher WHERE TeachName LIKE ‘%a’;

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.

>> SELECT TeachName, subject FROM data.teacher WHERE TeachName LIKE ‘%am%’;

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’.

>> SELECT TeachName, subject FROM data.teacher WHERE TeachName LIKE ‘S%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.

>> SELECT * FROM data.record;

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.

>> SELECT * FROM data.record WHERE Name LIKE ‘Za___’;

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.

>> SELECT * FROM data.record WHERE Name LIKE ‘___a%’;

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.

>> SELECT * FROM data.record WHERE Name LIKE ‘%_a’;

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’.

>> SELECT * FROM data.record WHERE Country LIKE ‘_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.

>> SELECT * FROM data.record WHERE Country NOT LIKE ’%i%;

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.

>> SELECT * FROM data.record WHERE CITY LIKE ’%\_20’;

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.

>> SELECT * FROM data.record WHERE CITY LIKE ’%\%%’;

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.

>> SELECT * FROM data.record WHERE CITY LIKE ’%\/’;

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.

from Linux Hint https://ift.tt/3kxE0eX