In this tutorial, you will learn how to use the MySQL LIKE operator in the WHERE clause of a SELECT statement to retrieve records based on pattern matching. You can use MySQL LIKE to search for any records that meet the specified pattern.
MySQL supports two wildcard characters, the percent sign (%) and the underscore sign (_), which you can use with the LIKE operator.
Using the LIKE operator when specifying a condition in a WHERE clause, you can use the special characters '%' and '_' for pattern matching when comparing a column's value to a string match.
The percent sign (%) matches any string of zero, a single character or more, and the underscore sign (_) matches any single character.
Learn more:
MySQL LIKE Syntax
When specifying a condition in the WHERE clause, you can use the LIKE operator to perform pattern matching on the values of a column using special characters. The syntax is as follows:
SELECT column1, column2, ..., columnN
FROM table
WHERE columnN LIKE pattern;
Or:
SELECT * FROM table
WHERE columnN LIKE pattern;
The following is a list of some examples showing different LIKE operators with % and _ wildcards:
| LIKE Operator | Description |
|---|---|
| WHERE employee_name LIKE 'J%' | Search for any values that start with "J" |
| WHERE employee_name LIKE '%J' | Search for any values that end with "J" |
| WHERE employee_name LIKE '_%_%_s' | Search for values that end in "S" and are at least four characters long |
| WHERE employee_name LIKE 'J%s' | Search for any values that start with "J" and end with "s" |
| WHERE employee_name LIKE '_a_e%' | Search for values where there is "a" in the 2nd position and "e" in the 4th position |
| WHERE employee_name LIKE 'j_%' | Searches for values that start with "J" and have a length of at least two characters |
Examples
Suppose you have a table named employees, and it consists of 2 columns: id and name.
Example 1
The following SQL query retrieves all employees whose names begin with J:
SELECT id, name FROM employees
WHERE name LIKE 'J%';
Example 2
The following SQL query will retrieve all employees whose names end with s:
SELECT * FROM employees
WHERE name LIKE '%s';
Example 3
The following SQL query retrieves all employees whose names contain am at any position:
SELECT * FROM employees
WHERE name LIKE '%am%';
Example 4
In this example, suppose you want to get all employees whose names start with J and end with s and are at least eight characters long.
The SQL query is as follows:
SELECT * FROM employees
WHERE name LIKE 'J_%_%__%_%_s';
Example 5
In this example, suppose you want to get all employees whose names start with J or R.
The MySQL query is as follows:
SELECT * FROM employees
WHERE name LIKE 'J%' OR name LIKE 'r%';
Summary
In this tutorial, you have learned how to use the MySQL LIKE operator in the WHERE clause of a SELECT statement to retrieve records from a specific MySQL database that satisfy the specified pattern.
MySQL supports two wildcard characters (%) and () that can be used with the LIKE operator. The wildcard character % matches any character sequence, and matches any single character.