With SQL LIKE clause, we can match a specific pattern from a data value, we mostly use LIKE clause for search purpose. With LIKE operator we use two wildcards, and these two wildcards are used to create search patterns:
- % (Percentage)- This symbol represents zero or more than zero characters.
- _ (Underscore) - This symbol represents a single character.
LIKE operator Syntax
To use LIKE operator, follow this syntax:
SELECT column_name_1, column_name_2, ... FROM table_name WHERE column_name LIKE search_pattern;
Syntax Example With the help of two wildcards of LIKE operators, we can generate different search patterns.
| Wildcard | Pattern | Description |
| % |
'ai%' |
Look for that data which value starts with “ ai ” |
| % |
'%ai%' |
Look for that data which have ai in it |
| _ % |
'_ai%' |
Look for that data which second value is a and third value is i. |
| _ % _ % |
'a_%_%' |
Look for that data which value starts with a and have at least 3 characters. |
| % |
'%a' |
Look for that data which value ends with a |
| _ % |
'_a%i' |
Look for that data which 2 nd value is a and ends with i |
| _ |
'a___i' |
Look for a value which total length is 5 and its stating value is a and ending value is i |
SQL Like Example
For the example considered this Students table:
+------+--------+------+--------+-------+ | id | name | age | grades | marks | +------+--------+------+--------+-------+ | 1 | Luffy | 16 | A | 970 | | 2 | Naruto | 18 | A | 960 | | 3 | Zoro | 20 | A | 940 | | 4 | Sanji | 21 | B | 899 | | 5 | Nami | 17 | B | 896 | | 6 | Robin | NULL | B | 860 | +------+--------+------+--------+-------+
Query: Display those students records whose name starts with N
SELECT * FROM students WHERE name LIKE 'N%';
Output
+------+--------+------+--------+-------+ | id | name | age | grades | marks | +------+--------+------+--------+-------+ | 2 | Naruto | 18 | A | 960 | | 5 | Nami | 17 | B | 896 | +------+--------+------+--------+-------+
Query: Display those students records whose name ends with i:
SELECT * FROM students WHERE name LIKE '%i';
Output
+------+-------+------+--------+-------+ | id | name | age | grades | marks | +------+-------+------+--------+-------+ | 4 | Sanji | 21 | B | 899 | | 5 | Nami | 17 | B | 896 | +------+-------+------+--------+-------+
Query: Display those students records whose has the letter r in their name
SELECT * FROM students WHERE name LIKE '%r%';
Output
+------+--------+------+--------+-------+ | id | name | age | grades | marks | +------+--------+------+--------+-------+ | 2 | Naruto | 18 | A | 960 | | 3 | Zoro | 20 | A | 940 | | 6 | Robin | NULL | B | 860 | +------+--------+------+--------+-------+
Query: Display those students records whose name second letter is a
SELECT * FROM students WHERE name LIKE '_a%';
Output
+------+--------+------+--------+-------+ | id | name | age | grades | marks | +------+--------+------+--------+-------+ | 2 | Naruto | 18 | A | 960 | | 4 | Sanji | 21 | B | 899 | | 5 | Nami | 17 | B | 896 | +------+--------+------+--------+-------+
Query: Display those students records whose name start with N and their name length must be greater than 4.
SELECT * FROM students WHERE name LIKE 'n____%';
Output
+------+--------+------+--------+-------+ | id | name | age | grades | marks | +------+--------+------+--------+-------+ | 2 | Naruto | 18 | A | 960 | +------+--------+------+--------+-------+
Summary
- LIKE operator is used to search for the matching pattern from the data set.
- To create a pattern we have two LIKE wildcards %(percentage) and _(Underscore)
- % represents zero or more than zero characters
- _ represent a single character.
- We always use LIKE operator with the WHERE clause.
People are also reading: