MySQL | LIKE
Start your free 7-days trial now!
MySQL's LIKE operator can be used to search for values with a particular string pattern. There are two wildcards that we can use with the LIKE operator: % (percent) and _ (underscore).
Wildcard | Description |
|---|---|
| Takes the place of zero, one or multiple characters |
| Takes the place of one character |
Syntax
SELECT column_name(s)FROM table_nameWHERE column_name LIKE value;
Examples
Consider the following table about some students:
student_id | fname | lname | day_enrolled | age | username |
|---|---|---|---|---|---|
1 | Sky | Towner | 2015-12-03 | 17 | stowner1 |
2 | Ben | Davis | 2016-04-20 | 19 | bdavis2 |
3 | Travis | Apple | 2018-08-14 | 18 | tapple3 |
4 | Arthur | David | 2016-04-01 | 16 | adavid4 |
5 | Benjamin | Town | 2014-01-01 | 17 | btown5 |
The above sample table can be created using the code here.
Percentage wildcard
To return all students whose lname starts with 'Dav':
SELECT * FROM students WHERE lname LIKE 'Dav%';
+------------+--------+-------+--------------+------+----------+| student_id | fname | lname | day_enrolled | age | username |+------------+--------+-------+--------------+------+----------+| 2 | Ben | Davis | 2016-04-20 | 19 | bdavis2 || 4 | Arthur | David | 2016-04-01 | 16 | adavid4 |+------------+--------+-------+--------------+------+----------+
Note that the % represents any number of characters after 'Dav'.
To return all usernames that contain 'dav':
SELECT * FROM students WHERE username LIKE '%dav%';
+------------+--------+-------+--------------+------+----------+| student_id | fname | lname | day_enrolled | age | username |+------------+--------+-------+--------------+------+----------+| 2 | Ben | Davis | 2016-04-20 | 19 | bdavis2 || 4 | Arthur | David | 2016-04-01 | 16 | adavid4 |+------------+--------+-------+--------------+------+----------+
Placing % at the start and end allows for a double ended search.
LIKE is not synonymous to contains. For instance:
SELECT * FROM students WHERE lname LIKE 'Dav';
Empty set (0.00 sec)
Even though last names 'Davis' and 'David' contain 'Dav', they are not returned without the wildcard.
Underscore wildcard
To return students with three letter first names that begin with an 'S' and end with a 'y':
SELECT * FROM students WHERE fname LIKE 'S_y';
+------------+-------+--------+--------------+------+----------+| student_id | fname | lname | day_enrolled | age | username |+------------+-------+--------+--------------+------+----------+| 1 | Sky | Towner | 2015-12-03 | 17 | stowner1 |+------------+-------+--------+--------------+------+----------+
Here, the second character in the three character fname can be anything. However, the first character must be 'S' and third character must be 'y'.
Combining wildcards
To return students whose username has 't' as the second character:
SELECT * FROM students WHERE username LIKE '_t%';
+------------+----------+--------+--------------+------+----------+| student_id | fname | lname | day_enrolled | age | username |+------------+----------+--------+--------------+------+----------+| 1 | Sky | Towner | 2015-12-03 | 17 | stowner1 || 5 | Benjamin | Town | 2014-01-01 | 17 | btown5 |+------------+----------+--------+--------------+------+----------+
