LIKE operator

The LIKE operator is used in conditional queries when we want to find out whether a string matches a certain pattern.

For example, we have a Users table that has an email field:

MySQL
SELECT name, email FROM Users;

Suppose we want to find all users whose email is in the second-level domain "hotmail". That is, we need to select only those records that meet the following condition:

• after the "@" symbol, "hotmail" follows
• after "hotmail," a "." symbol follows and then any sequence of characters

For such non-trivial searches on string fields, we need the LIKE operator.

Syntax

MySQL
... WHERE table_field [NOT] LIKE string_pattern

The pattern may include the following special characters:

So our query for finding users in the "hotmail" domain might look like this:

MySQL
SELECT name, email FROM Users
WHERE email LIKE '%@hotmail.%'

Examples

• MySQL
  ... WHERE table_field LIKE 'text%'
  

  Matches any strings beginning with "text"

• MySQL
  ... WHERE table_field LIKE '%text'
  

  Matches any strings ending with "text"

• MySQL
  ... WHERE table_field LIKE '_ext'
  

  Matches strings with a length of 4 characters, with the last 3 characters required to be "ext". For example, the words "text" and "next"

• MySQL
  ... WHERE table_field LIKE 'begin%end'
  

  Matches strings starting with "begin" and ending with "end"

By default, MySQL is not case-sensitive.

ESCAPE character

The ESCAPE character is used to escape special characters (% and \). In case you need to find strings, you can use the ESCAPE character.

For example, if you want to retrieve the job IDs of tasks with a progress of 3%:

MySQL
SELECT job_id FROM Jobs
WHERE progress LIKE '3!%' ESCAPE '!';

If we didn't escape the wildcard character, the query would include everything starting with 3.