postgreSQL LIKE Operator

The postgreSQL Like operator is used to find specific patterns in a column. Two wildcards are used in conjunction with the LIKE operator.If the string matches the supplied pattern, the LIKE expression is true and if LIKE is true then the NOT LIKE expression is false.
% :-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.

lfcdb=# SELECT * FROM Customer
WHERE CustomerSalary LIKE '500%';
   


The result for the respective postgreSQL 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.

lfcdb=# SELECT * FROM Customer
WHERE CustomerSalary LIKE '%050';   


The result for the respective postgreSQL 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.