SQL LIKE

 

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 clause of the SELECTUPDATE, and DELETE statements.

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

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

The following table illustrates some patterns and their meanings:

ExpressionMeaning
LIKE 'Kim%'Begins with Kim
LIKE '%er'Ends with er
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.

employees_table

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%';
SQL LIKE begin with Da example

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';
SQL LIKE end with er example

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%';
SQL LIKE last name contains 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__';
SQL LIKE Underscore wildcard example

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 LIKE wildcards combination

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;
SQL NOT LIKE example

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

Reactions

Post a Comment

0 Comments