While working with the MySQL database we do use the WHERE operator to ensure we set a condition to the result we want to return. Mostly while using the WHERE clause, we do use either AND or OR operators to define the conditions to match the results.
At times we find in scenarios where you have given users the option to search for a product or item in the database by entering any word or search term. During this scenario, we cannot use the AND or OR operators to set the condition since the users do not know the exact wording or name used to define the values in the database.
At this point where the user can enter any key term and not limit the user by returning zero results and yet there is a term that is almost the same as the one that was searched by the user, we use the LIKE operator.
The LIKE operator in MySQL tends to be lenient with the user and returns the results that are closer or look like the ones the user has searched. Unlike the AND and OR operators where it must match exactly as the search term given, the LIKE operator returns the like alike result.
In this article, we shall discuss how to use the LIKE operator in SQL and show the different ways it is used to compare results and the patterns applied to match a certain format.
The LIKE operator is used in a WHERE clause to compare a value to other similar values using wildcards. There are two wildcards that are used together with the LIKE operator, that is, the percent sign (%) and the underscore sign (_)
- The % sign represents any number of characters, either 0, 1, or more than 1
- The _ sign represents only one single character (more will be explained in the demos)
LIKE syntax
The LIKE operator must occur after the WHERE clause is defined
SELECT * FROM table_name WHERE column_1 LIKE pattern;
Percent (%) sign wildcard
Like we have described above, the % sign can represent any number of characters. The format or pattern of using the % sign is as follows
Suppose we have a search value x. The possible uses of % sign are;
- 'x%' means that the result finds any value that starts with x
SELECT * FROM table_name WHERE column_1 LIKE 'x%';
- '%x' means that the result finds any value that ends with x
SELECT * FROM table_name WHERE column_1 LIKE '%x';
- '%x%' means that the result finds any value that has x in any position
SELECT * FROM table_name WHERE column_1 LIKE '%x%';
- 'x%y' means that the result finds any value that starts with x and ends with y
SELECT * FROM table_name WHERE column_1 LIKE 'x%y';
Underscore (_) sign wildcard
The _ sign represents only one single character meaning that when only one underscore is defined it only finds a result for only one character in the result. If you want to get results for more than one character you define as many underscore signs to fit your result.
The possible patterns for using the _ wildcard are as follows
- '_x%' means that the result finds any value that has x in the second position
SELECT * FROM table_name WHERE column_1 LIKE '_x%';
- 'x_%' means that the result finds any value that starts with x and it must have two or more characters
SELECT * FROM table_name WHERE column_1 LIKE 'x_%';
- 'x__%' means that the result finds any value that starts with x and it must have three or more characters (note that there are 2 underscore signs)
SELECT * FROM table_name WHERE column_1 LIKE 'x__%';
- '___x%' means that the result finds any value that has x at the fourth position (note that there are 3 underscore signs)
SELECT * FROM table_name WHERE column_1 LIKE 'x__%';
An example
Suppose we have a database table with names of users and a column named customer_name.
We want to search for all users with the name John at any position
The query to return the result will be
SELECT * FROM users WHERE customer_name LIKE '%John%'
That’s it for this article, hope you have learned how to use the LIKE operator in MySQL. For the patterns we have defined for each wildcard, you can practice with as many examples as possible in your database.
