MySQL LIKE Operator

                MySQL LIKE Operator

The LIKE operator is used in a WHERE clause to search for a specified pattern in a column.

There are two wildcards often used in conjunction with the LIKE operator:

• The percent sign (%) represents zero, one, or multiple characters
• The underscore sign (_) represents one, single character

The percent sign and the underscore can also be used in combinations!

LIKE Syntax

SELECT column1, column2, ...
FROM table_name
WHERE columnN LIKE pattern;

Tip: You can also combine any number of conditions using AND or OR operators.

Here are some examples showing different LIKE operators with '%' and '_' wildcards:

LIKE Operator and their Description

WHERE LastName LIKE ‘a%’ : Finds any values that start with “a”

WHERE LastName LIKE ‘%a’ : Finds any values that end with “a”

WHERE LastName LIKE ‘%or%’ : Finds any values that have “or” in any position

WHERE LastName LIKE ‘_r%’ : Finds any values that have “r” in the second position

WHERE LastName LIKE ‘a_%’ : Finds any values that start with “a” and are at least 2 characters in length

WHERE LastName LIKE ‘a__%’ : Finds any values that start with “a” and are at least 3 characters in length

WHERE LastName LIKE ‘a%o’ : Finds any values that start with “a” and ends with “o”

Demo Database

The table below shows the complete “Employees” table from the sample database:

SQL LIKE Examples

The following SQL statement selects all Employees with a LastName starting with “a”:

Example

SELECT * FROM Employees
WHERE LastName LIKE 'a%';

The following SQL statement selects all Employees with a LastName ending with “a”:

Example

SELECT * FROM Employees
WHERE LastName LIKE '%a';

The following SQL statement selects all Employees with a LastName that have “or” in any position:

Example

SELECT * FROM Employees
WHERE LastName LIKE '%or%';

The following SQL statement selects all Employees with a LastName that have “r” in the second position:

Example

SELECT * FROM Employees
WHERE LastName LIKE '_r%';