Pattern matching with LIKE and ILIKE in PostgreSQL

Introduction

We use pattern matching to find text that fits a certain shape or pattern. LIKE and ILIKE help us search for words or parts of words inside a bigger text.

Finding all customers whose names start with 'A'.

Searching for email addresses that contain 'gmail'.

Looking for products with descriptions that include the word 'fresh'.

Filtering a list of cities that end with 'ville'.

Checking if a comment contains a specific phrase regardless of uppercase or lowercase letters.

Syntax

PostgreSQL

SELECT column_name
FROM table_name
WHERE column_name LIKE 'pattern';

-- For case-insensitive search in PostgreSQL:
SELECT column_name
FROM table_name
WHERE column_name ILIKE 'pattern';

The LIKE operator matches patterns with case sensitivity.

The ILIKE operator matches patterns without case sensitivity (PostgreSQL only).

Examples

Finds names starting with 'A' (case sensitive).

PostgreSQL

SELECT name FROM users WHERE name LIKE 'A%';

Finds names ending with 'smith', ignoring case.

PostgreSQL

SELECT name FROM users WHERE name ILIKE '%smith';

Finds emails that end with '@gmail.com'.

PostgreSQL

SELECT email FROM contacts WHERE email LIKE '[e-mail]';

Finds cities containing 'ville' anywhere, ignoring case.

PostgreSQL

SELECT city FROM locations WHERE city ILIKE '%ville%';

Sample Program

This example creates a table of employees, adds some data, then shows how to find names starting with 'A' using LIKE and emails containing 'gmail' using ILIKE.

PostgreSQL

CREATE TABLE employees (
  id SERIAL PRIMARY KEY,
  name VARCHAR(100),
  email VARCHAR(100)
);

INSERT INTO employees (name, email) VALUES
('Alice Johnson', '[e-mail]'),
('Bob Smith', '[e-mail]'),
('carol White', '[e-mail]'),
('David Brown', '[e-mail]');

-- Case sensitive search for names starting with 'A'
SELECT name FROM employees WHERE name LIKE 'A%';

-- Case insensitive search for emails containing 'gmail'
SELECT email FROM employees WHERE email ILIKE '%gmail%';

Important Notes

LIKE is case sensitive, so 'A%' matches 'Alice' but not 'alice'.

ILIKE is PostgreSQL-specific and ignores case, useful for user-friendly searches.

Use % as a wildcard for any number of characters, and _ for a single character.

Summary

LIKE matches patterns with case sensitivity.

ILIKE matches patterns ignoring case (PostgreSQL only).

Use % and _ as wildcards to create flexible search patterns.