LIKE operator is used to searching the records, It has two patterns.
1) % pattern It is used to search for single or multiple characters.
2) _ pattern It is used to search for one character.
In SELECT Statement
Syntax:-
SELECT column(s)
FROM table_name
WHERE column
LIKE pattern
Example:- Suppose we have employees table which has 7 records
+----+------------+-----------+----------------+-----------+
| id | first_name | last_name | email | country |
+----+------------+-----------+----------------+-----------+
| 1 | John | Tailor | john@abc.com | USA |
| 2 | Rom | Tailor | rom@abc.com | USA |
| 3 | Andrew | Symonds | andrew@abc.com | Australia |
| 4 | Miacle | Tailor | miacle@abc.com | Australia |
| 5 | Sachin | Tendulkar | sachin@abc.com | India |
| 6 | Virat | Kohli | virat@abc.com | India |
| 7 | rohit | NULL | rohit@abc.com | India |
Use % pattern
CASE 1:- Find the record that has “T” in the first position in the last_name field.
SELECT * FROM employees where last_name LIKE 'T%';
Output:-
+----+------------+-----------+----------------+-----------+
| id | first_name | last_name | email | country |
+----+------------+-----------+----------------+-----------+
| 1 | John | Tailor | john@abc.com | USA |
| 2 | Rom | Tailor | rom@abc.com | USA |
| 4 | Miacle | Tailor | miacle@abc.com | Australia |
| 5 | Sachin | Tendulkar | sachin@abc.com | India |
+----+------------+-----------+----------------+-----------+
4 rows in set (0.00 sec)
CASE 2:- Find the record that has “in” characters in the last position in the first_name field.
SELECT * FROM employees where first_name LIKE '%in';
Output:-
+----+------------+-----------+----------------+---------+
| id | first_name | last_name | email | country |
+----+------------+-----------+----------------+---------+
| 5 | Sachin | Tendulkar | sachin@abc.com | India |
+----+------------+-----------+----------------+---------+
1 row in set (0.00 sec)
CASE 3:- Find the record that has “ai” in any position in the last_name field.
SELECT * FROM employees where last_name LIKE '%ai%';
Output:-
+----+------------+-----------+----------------+-----------+
| id | first_name | last_name | email | country |
+----+------------+-----------+----------------+-----------+
| 1 | John | Tailor | john@abc.com | USA |
| 2 | Rom | Tailor | rom@abc.com | USA |
| 4 | Miacle | Tailor | miacle@abc.com | Australia |
+----+------------+-----------+----------------+-----------+
3 rows in set (0.00 sec)
Use _ pattern
CASE 1:- Find the record that has the second character “o” in the first_name field.
SELECT * FROM employees where first_name LIKE '_o%';
Output:-
+----+------------+-----------+---------------+---------+
| id | first_name | last_name | email | country |
+----+------------+-----------+---------------+---------+
| 1 | John | Tailor | john@abc.com | USA |
| 2 | Rom | Tailor | rom@abc.com | USA |
| 7 | rohit | NULL | rohit@abc.com | India |
+----+------------+-----------+---------------+---------+
3 rows in set (0.00 sec)
CASE 2:- Find the record that has a character start with “Indi” in the email field.
SELECT * FROM employees where country LIKE 'Indi_%';
Output:-
+----+------------+-----------+----------------+---------+
| id | first_name | last_name | email | country |
+----+------------+-----------+----------------+---------+
| 5 | Sachin | Tendulkar | sachin@abc.com | India |
| 6 | Virat | Kohli | virat@abc.com | India |
| 7 | rohit | NULL | rohit@abc.com | India |
+----+------------+-----------+----------------+---------+
3 rows in set (0.00 sec)