LIKE in PostgreSQL

The LIKE operator in PostgreSQL is used for pattern matching in text (string) values. It allows you to search for patterns using wildcard characters.

Syntax

column_name LIKE 'pattern'

• Case-Sensitive: LIKE is case-sensitive in PostgreSQL.
• Case-Insensitive Alternative: Use ILIKE (not case-sensitive).

Wildcards in LIKE

Examples of LIKE

1. Matching Strings That Start with a Certain Pattern

SELECT * FROM employeesWHERE name LIKE 'A%';

✅ Retrieves employees whose names start with 'A' (e.g., Alice, Andrew).

2. Matching Strings That End with a Certain Pattern

SELECT * FROM productsWHERE product_name LIKE '%Phone';

✅ Retrieves products whose names end with 'Phone' (e.g., iPhone, Samsung Phone).

3. Matching Strings That Contain a Pattern

SELECT * FROM ordersWHERE customer_name LIKE '%John%';

✅ Retrieves orders where the customer name contains 'John' (e.g., John Doe, Mary Johnson).

4. Matching Strings with a Specific Character at a Certain Position

SELECT * FROM employeesWHERE emp_code LIKE 'A_3%';

✅ Finds employee codes where:

• Starts with 'A'
• Second character is any single character
• Third character is '3'
• Followed by any characters (e.g., A23XX, AB3YZ).

Using ILIKE for Case-Insensitive Matching

SELECT * FROM customersWHERE name ILIKE 'john%';

✅ Finds names like John, john, JOHN, ignoring case.

Using NOT LIKE

SELECT * FROM usersWHERE email NOT LIKE '%@gmail.com';