MySQL LIKE Operator Pattern Matching and Examples

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


See also:
MySQL SUBSTRING_INDEX Function with Examples
MySQL EXISTS Operator with Examples
MySQL ROW_NUMBER Function with Examples
MySQL CONCAT() Function | Concatenate Strings in MySQL
MySQL IN Operator with Examples

Leave a Comment