LIKE Operator MySQL Essential Training

LIKE Operator MySQL Essential Training

MySQL

LIKE Operator MySQL Essential Training




In this LIKE Operator MySQL Essential Training lesson will teach you what is the LIKE operator and its functionalities. The LIKE operator selects data based on the patterns.
The LIKE Operator allows us to select data from the table on a specified pattern. Therefore, this LIKE is can works with where clause and select statement. This like operator is increases the query performance.
In this LIKE Operator MySQL Essential Training, the MySQL gives us a two wild card characters percentage (%) and underscore(_).
The percentage (%) – which allows you to match any string of zero or more characters
The underscore (_) – which is used to select any single character.
LIKE Operator MySQL Essential Training – Percentage(%) Wild Card
The real time experience will gives us a more understanding than the theory. Lets jump in to the examples. See the following customers table using the Like Operator.
If we wants to finds the employees who’s first name are started with a letter ‘j’, then you can use the wild card character % at the end of the pattern as follows:

LIKE Operator MySQL Essential Training
MySQL scans the whole employees table to find an employee whose first name starts with character ‘j’ and followed by any number of characters.
MySQL can also has the capability of find the last names end with the characters like ‘na’. eg: Peterson, Thompson and etc with the power of wild card characters.

LIKE Operator MySQL Essential Training
If you know the searched string is embedded inside in the column, you can use the percentage ( % ) wild card at the beginning and the end of the pattern.
For example, to find all employees whose first names contain “on” string, you use the following query with pattern %on%

LIKE Operator MySQL Essential Training




In the above example we used the query for return which are all the rows contains the character “j” in the column first_name.
LIKE Operator MySQL Essential Training – Underscore(_) Wild Card
To find employee whose first name starts with T, ends with m and contains any single character between e.g., Tom , Tim, you use the underscore wild card to construct the pattern as follows:

LIKE Operator MySQL Essential Training
LIKE operator MySQL Essential Training – NOT operator
This like operator also works with the NOT operator to find the patterns which are doesn’t match with the result.
Suppose you want to search for employees in a table whose last name does not start with character B, you can use the NOT LIKE with the pattern as the following query:

LIKE Operator MySQL Essential Training
In the above example we filter and don’t show the first_name which are starts with the character ‘R’.
LIKE operator MySQL Essential Training – ESCAPE clause
Sometimes the pattern, which you want to match, contains wild card character e.g., 10%, _20, etc. In these cases, you can use the ESCAPE clause to specify the escape character so that MySQL interprets the wild card character as a literal character. If you don’t specify the escape character explicitly, the backslash character \ is the default escape character.
For example, if you want to find product whose product price contains string _5 , you can use the pattern %\_5% as the following query:

LIKE Operator MySQL Essential Training
As conclude at the end of this lesson, we hope this lesson will gives you an clear idea about the LIKE operator and its functionalities with many clauses and other related operators.




Leave a Reply