SQL LIKE

Summary: in this tutorial, you will learn how to use the SQL LIKE operator to test whether an expression matches a pattern.

Introduction to SQL LIKE operator

Sometimes, it is useful to test whether an expression matches a specific pattern, for example, to find all employees whose first names start with Da or Sh. In these cases, you need to use the LIKE operator.

The LIKE operator tests whether an expression matches a specific pattern. See the following syntax:

expression LIKE pattern

If the expression matches the pattern, the LIKE operator returns true. Otherwise, it returns false.

The LIKE the operator is often used in  WHERE the clause of the SELECT, UPDATE, and DELETE statements.

To construct a pattern, you use two of the SQL wildcard characters:

•  % percent sign matches zero, one, or more characters
•  _ The underscore sign matches a single character.

The following table illustrates some patterns and their meanings:

Expression	Meaning
LIKE 'Kim%'	Begins with Kim
LIKE '%er'	Ends wither
LIKE '%ch%'	Contains ch
LIKE 'Le_'	Begins with Le and is followed by at most one character e.g., Les, Len…
LIKE '_uy'	Ends with uy and is preceded by at most one character e.g., guy
LIKE '%are_'	Contains are, begins with any number of characters and ends with at most one character
LIKE '_are%'	Contains are, begins with at most one character and ends with any number of characters

If you want to match the wildcards % or _, you must use the backslash character \ to escape it. In case you want to use a different escape character rather than the backslash, you use  ESCAPE clause in the LIKE expression as follows:

expression LIKE pattern ESCAPE escape_character

SQL LIKE operator examples

We will use the employees the table in the sample database for the demonstration.

To find all employees whose first names begin with Da, you use the pattern Da% as shown in the following statement:

SELECT
	employee_id,
	first_name,
	last_name
FROM
	employees
WHERE
	first_name LIKE 'Da%';

To find all employees whose first names end with er, you use the pattern %er as follows:

SELECT
	employee_id,
	first_name,
	last_name
FROM
	employees
WHERE
	first_name LIKE '%er';

The following statement finds all employees whose last names contain the word an:

SELECT
	employee_id,
	first_name,
	last_name
FROM
	employees
WHERE
	last_name LIKE '%an%';

The following statement retrieves employees whose first names start with Jo and are followed by at most 2 characters:

SELECT
	employee_id,
	first_name,
	last_name
FROM
	employees
WHERE
	first_name LIKE 'Jo__';

The following statement selects employees whose first names start with any number of characters and are followed by at most one character.

SELECT
	employee_id,
	first_name,
	last_name
FROM
	employees
WHERE
	first_name LIKE '%are_';

SQL NOT LIKE operator

To negate the result of the LIKE operator, you use the NOT operator as follows:

expression NOT LIKE pattern ESCAPE escape_character

For example, to find all employees whose first names begin with S but not begin with Sh, you use the following statement:

SELECT
	employee_id,
	first_name,
	last_name
FROM
	employees
WHERE
	first_name LIKE 'S%'
AND first_name NOT LIKE 'Sh%'
ORDER BY
	first_name;

In this tutorial, you have learned how to use the SQL LIKE operator to select values that match a specific pattern.