PostgreSQL- Like Clause

priya s
YavarTechWorks
Published in
2 min readDec 31, 2022

Hi everyone, In this blog we are going to see about Like clause in PostgreSQL.

The PostgreSQL LIKE operator is used to match text values against a pattern. There are two wildcards used along with LIKE operator namely,

  1. The percent sign (%),
  2. The Underscore(_)

The % sign represents zero, one or multiple numbers or characters. The underscore represents a single number or character.

Let us consider the above table which has 5 records of id, name, age, address and salary.

Example:1

Syntax: SELECT * FROM TABLE_NAME WHERE COLUMN LIKE ‘XXX%’

DESCRIPTION: It will find any values that starts with XXX.

Note: In PostgreSQL LIKE is compares string only. Hence casting the integer column to string is necessary.

Example 2:

Syntax: SELECT * FROM TABLE_NAME WHERE COLUMN LIKE ‘%XXX%’;

DESCRIPTION : It will find any values that have XXX in any position.

Example 3:

Syntax: SELECT * FROM TABLE_NAME WHERE COLUMN ILIKE ‘%XXX%’;

DESCRIPTION : It will find any values that have XXX in any position where ILIKE means it will retrieve all the records irrespective of their cases.

Example 4:

Syntax: SELECT * FROM TABLE_NAME WHERE COLUMN LIKE ‘_XX’;

DESCRIPTION : It will find the values that exactly matches XX in the second and third position.

Example 5:

Syntax: SELECT * FROM TABLE_NAME WHERE COLUMN LIKE ‘_XX%’;

DESCRIPTION : It will find any values that have XX in the second and the third position.

Hope you found it useful!!

--

--