SQL LIKE Operator

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)