SQL LIKE Operator

The SQL Like operator is used to find specific patterns in a column. Two wildcards are used in conjunction with the LIKE operator.
% :-The percent sign
_ :-The underscore sign
The percent sign represents zero, one or several characters while the underscore represents a single character

LIKE Operator Syntax :

The syntax of the LIKE Operator is −

SELECT column1, column2, ...
FROM table-name
WHERE columnN LIKE pattern;   

Statement Description
LIKE '500%' The statement searches for any value starting with 500.
LIKE '%500%' The statement searches for any value that have 500 in any position.
LIKE '_00%' The statement searches for any value that have 00 in the second and third positions.
LIKE '5_%_%' The statement searches for any value that start with 5 and are at least 3 characters in length.
LIKE '%5' The statement searches for any value that end with 5.
LIKE '_5%2' The statement searches for any value that have a 5 in the second position and end with a 2.
LIKE '5___2' The statement searches for any value in a five-digit number that start with 5 and end with 2.



LIKE Operator Example :

Consider the Customer table with the following records -

CustomerID CustomerName Age Address CustomerSalary
1 Aarav 28 Udaipur 50000
2 Vivaan 25 Mumbai 30025
3 Reyansh 28 Chennai 35050
4 Muhammad 24 Udaipur 50075
5 Sai 30 Mumbai 27050


TEST CASE 1 :- The following code is an example, which would fetch the record from Customer table where CustomerSalary start with 500.

SELECT * FROM Customer
WHERE CustomerSalary LIKE '500%';
   


The result for the respective sql query is as follows −

CustomerID CustomerName Age Address CustomerSalary
1 Aarav 28 Udaipur 50000
4 Muhammad 24 Udaipur 50075



TEST CASE 2:- The following code is an example, which would fetch the record from Customer table where CustomerSalary end with 050.

SELECT * FROM Customer
WHERE CustomerSalary LIKE '%050';   


The result for the respective sql query is as follows −

CustomerID CustomerName Age Address CustomerSalary
3 Reyansh 28 Chennai 35050
5 Sai 30 Mumbai 27050





Visit :


Discussion


* You must be logged in to add comment.