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;
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
... 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:
SELECT name, email FROM Users WHERE email LIKE '%@hotmail.%'
Examples
-
... WHERE table_field LIKE 'text%'
Matches any strings beginning with "text"
-
... WHERE table_field LIKE '%text'
Matches any strings ending with "text"
-
... 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"
-
... 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.
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.