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:

SELECT name, email FROM Users;
Bruce Willis[email protected]
George Clooney[email protected]
Kevin Costner[email protected]
Samuel L. Jackson[email protected]
Kurt Russell[email protected]

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.


... WHERE table_field [NOT] LIKE string_pattern

The pattern may include the following special characters:

%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:

SELECT name, email FROM Users
WHERE email LIKE '%@hotmail.%'
Bruce Willis[email protected]
Kevin Costner[email protected]
Jennifer Lopez[email protected]
Harrison Ford[email protected]
Michael Douglas[email protected]
Catherine Zeta-Jones[email protected]


  • 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%:

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.