MySQL LIKE Operator with Examples

In this tutorial, you’ll learn how to use the MySQL LIKE operator in a WHERE clause in 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, percentage sign (%) and underscore sign (_), which you can use with the LIKE operator. 

The percentage sign (%) matches any string of zero, one, or more characters, and the underscore sign (_) matches any single character.

MySQL LIKE Syntax

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 OperatorDescription
WHERE employee_name LIKE 'J%'Find any values that start with J
WHERE employee_name LIKE '%J'Find any values that end with J
WHERE employee_name LIKE '_%_%_s'Find any values that end with s and are at least 4 characters in length
WHERE employee_name LIKE 'J%s'Find any values that start with J and end with s
WHERE employee_name LIKE '_a_e%'Find any values that have a in the 2nd position and e in the 4th position
WHERE employee_name LIKE 'j_%'Find any values that start with J and are at least 2 characters in length

Examples

Suppose you have a table named employees, which consists of two columns: id and employee_name.

Example 1

The following SQL query retrieves all employees that employee_name starts with J:

SELECT id, employee_name FROM employees

WHERE employee_name LIKE 'J%';

Example 2

The following SQL query retrieves all employees that employee_name ends with s:

SELECT * FROM employees

WHERE employee_name LIKE '%s';

Example 3

The following SQL query retrieves all employees that employee_name contains am in any position:

SELECT * FROM employees

WHERE employee_name LIKE '%am%';

Example 4

In this example, let’s say you need to retrieve all employees that employee_name starts with J and ends with s, and are at least eight characters in length.

The SQL query is as follows:

SELECT * FROM employees

WHERE employee_name LIKE 'J_%_%__%_%_s';

Example 5

In this example, let’s say you need to retrieve all employees that employee_name starts with J or R;

The MySQL query is as follows:

SELECT * FROM employees

WHERE employee_name LIKE 'J%' OR employee_name LIKE 'r%';

In this tutorial, you’ve learned how to use the MySQL LIKE operator in a WHERE clause in SELECT to retrieve records that meet the specified pattern from a given MySQL database. 

MySQL supports two wildcard characters (%) and (_), which you can use with the LIKE operator. The wildcard character % matches any sequence of characters, and _ matches any single character.


See also:
MySQL SUBSTRING_INDEX Function with Examples
MySQL EXISTS Operator with Examples
MySQL ROW_NUMBER Function with Examples
MySQL CONCAT() Function with Examples
MySQL IN Operator with Examples

Leave a Comment