PostgreSQL- Like Clause
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,
- The percent sign (%),
- 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!!