There are three
different approaches(SIMILAR TO Regular Expressions, POSIX Regular Expressions,
and LIKE) to pattern matching provided by PostgreSQL but the LIKE operator is
the best one, it is easy to use and safe than other approaches. Using
wild-cards, the PostgreSQL LIKE operator matches text values against a pattern.
If any column text/value or any expression matches the pattern then the LIKE-operator will return the TRUE, if don't then false.
There many
scenarios where we have to apply LIKE operator in PostgreSQL, one of them I am
explaining here. Suppose we have a customer table and we need to see some
customer details but we don't know the customer's exact name but we remember
some part of his first name. Here LIKE operator will help you to find the
customer. Suppose the customer's full name is Dilip Kumar Singh but you have
remembered only Dilip, using a LIKE operator, you can search this customer and
can get his full details.
PostgreSQL allows two wild-cards in conjunction with LIKE operator
1- The
percent sign (%) use for matching any sequence of characters.
2- The
underscore sign(_) use for matching any single character.
Note:
If you are not using above wild-cards sign then LIKE operator works like equal
(=) operator.
Syntax: PostgreSQL LIKE operator
SELECT
Col_1,Col_2,Col_3... Col_n
FROM
tbl_name
WHERE
Col_1 LIKE <Expressions>
|
PostgreSQL LIKE operator Example
Suppose we have a customer table, using the below query you
can create the customer table.
CREATE TABLE public."Customer"
(
"Id"
serial NOT NULL,
"FName"
text COLLATE
pg_catalog."default",
"LName"
text COLLATE
pg_catalog."default",
"UserName"
text COLLATE
pg_catalog."default",
"Password"
text COLLATE
pg_catalog."default",
"Contact"
text COLLATE
pg_catalog."default",
"Address"
text NOT NULL DEFAULT 0.0,
CONSTRAINT "pk_Customer_Id" PRIMARY
KEY ("Id")
);
|
Insert some records in the customer table
INSERT INTO public."Customer"(
"FName", "LName", "UserName", "Password", "Contact", "Address")
VALUES ( 'Dilip Kumar', 'Singh', 'dilip@123', 'xyz', '9098876676', 'Noida');
INSERT INTO public."Customer"(
"FName", "LName", "UserName", "Password", "Contact", "Address")
VALUES ( 'Dilshad', 'Ahmad', 'dilshad@123', 'xyz', '8898646427', 'Delhi');
INSERT INTO public."Customer"(
"FName", "LName", "UserName", "Password", "Contact", "Address")
VALUES ( 'Ashish', 'Singh', 'ashish@123', 'xyz', '9087778765', 'Ghaziabad');
INSERT INTO public."Customer"(
"FName", "LName", "UserName", "Password", "Contact", "Address")
VALUES ( 'Mr Dilip', 'Singh', 'dk@123', 'xyz', '9087876654', 'Gorakhpur');
|
Examples of PostgreSQL LIKE operator.
SELECT "Id", "FName", "LName", "UserName",
"Password",
"Contact",
"Address"
FROM public."Customer" WHERE
"FName" LIKE
'%Dilip%';
This query will return records where any
values of Dilip are found in any position in the FName column.
Result:
SELECT "Id", "FName", "LName", "UserName",
"Password",
"Contact",
"Address"
FROM public."Customer" WHERE
"FName" LIKE
'Dilip%';
The query will return records where any values of Dil are found in the start
position in the FName column.
SELECT "Id", "FName", "LName", "UserName",
"Password",
"Contact",
"Address"
FROM public."Customer" WHERE
"FName" LIKE
'_ilip%';
The query will return records where any value has "ilip" at the second,
third, fourth, and fifth position respectively.
Result:
SELECT "Id", "FName", "LName", "UserName",
"Password",
"Contact",
"Address"
FROM public."Customer" WHERE
"FName" LIKE
'Dil_%_%';
The query will return records where any value has started with "Dil" at
the first second, third position respectively.
Result:
|