How to Use the LIKE Operator in MySQL

08/04/2021

Contents

In this article, you will learn how to use the LIKE operator in MySQL.

Using the LIKE operator in MySQL

The LIKE operator in MySQL is used to match patterns in a string. This operator can be used in the WHERE clause of SQL queries to select data based on a pattern match.

Syntax

The basic syntax of the LIKE operator in MySQL is as follows:

SELECT column_name(s)
FROM table_name
WHERE column_name LIKE pattern;

In this syntax, column_name is the name of the column you want to select data from, table_name is the name of the table, and pattern is the pattern you want to match. The pattern can include wildcard characters to match any character or a set of characters.

Examples

Suppose we have a table called “employees” with columns “id”, “name”, and “email”. We want to select all employees whose email address ends with “@company.com”.

SELECT name, email
FROM employees
WHERE email LIKE '%@company.com';

In this example, the % wildcard character is used to match any string of characters before “@company.com”. This query will return the names and email addresses of all employees whose email address ends with “@company.com”.

And suppose we have a table called “products” with columns “id”, “name”, and “description”. We want to select all products whose name starts with the letter “S”.

SELECT name, description
FROM products
WHERE name LIKE 'S%';

In this example, the S% pattern is used to match any string that starts with the letter “S”. This query will return the names and descriptions of all products whose name starts with the letter “S”.

Using wildcards

There are two wildcard characters that can be used with the LIKE operator in MySQL:

  • The % wildcard character matches any string of zero or more characters.
  • The _ wildcard character matches any single character.

Here are some examples of how to use these wildcard characters:

  • % example: WHERE name LIKE ‘%apple%’ matches any string that contains the word “apple”.
  • _ example: WHERE name LIKE ‘a_ _le’ matches any string that starts with the letter “a”, followed by any two characters, and ends with the letters “le”.

Using NOT LIKE

You can also use the NOT LIKE operator to select data that does not match a pattern. Here is an example:

SELECT name, email
FROM employees
WHERE email NOT LIKE '%@company.com';

In this example, the NOT LIKE operator is used to select all employees whose email address does not end with “@company.com”.

Using ESCAPE

If you want to search for wildcard characters as part of the pattern, you can use the ESCAPE keyword to specify an escape character. Here is an example:

SELECT name, description
FROM products
WHERE description LIKE '%50\% off' ESCAPE '\';

In this example, the \ character is used as the escape character. The % character is preceded by the escape character to indicate that it should be treated as a literal character, not a wildcard character.