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;
nameemail
Bruce Willisbarjam@hotmail.com
George Clooneytellis@me.com
Kevin Costnermetzzo@hotmail.com
Samuel L. Jacksonmoonlapse@outlook.com
Kurt Russellgator@live.com

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:

CharacterDescription
%Any sequence of characters (the number of characters in the sequence can be zero or more)
_Any single character

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

MySQL
SELECT name, email FROM Users
WHERE email LIKE '%@hotmail.%'
nameemail
Bruce Willisbarjam@hotmail.com
Kevin Costnermetzzo@hotmail.com
Jennifer Lopezbarjam@hotmail.com
Harrison Fordkostas@hotmail.com
Michael Douglastimtroyr@hotmail.com
Catherine Zeta-Jonesflakeg@hotmail.com

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.

Interactive exercise unavailable

To use the interactive exercise, a screen width of at least 600 pixels is required. Try opening this page on a computer or tablet.