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)