Skip to main content

LIKE Operator in PostgreSQL

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 to 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 customer's full name is Dilip Kumar Singh but you have remembered only Dilip, using LIKE operator, you can search this customer and can get his full details.

PostgreSQL allows two wild-cards in conjunction with LIKE operator

·         The percent sign (%) use for matching any sequence of characters.
·         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%';
This query will return records where any values of Dil are found in start position in the FName column.
 Result:

SELECT "Id", "FName", "LName", "UserName", "Password", "Contact", "Address"
                FROM public."Customer" WHERE "FName" LIKE '_ilip%';
This 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_%_%';
This query will return records where any value has started with "Dil" at the first second, third position respectively.
Result: