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.
... 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.%'
... 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.
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.