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.

Interactive Exercise

Now let's practice what we've learned!
In the exercise below, you need to distribute email addresses into columns according to the given LIKE patterns.