LIKE and Pattern Matching

The LIKE operator lets you search for patterns in text data using wildcards.

Wildcards

Basic LIKE Patterns

-- Names starting with 'A'
SELECT name FROM employees WHERE name LIKE 'A%';

-- Names ending with 'son'
SELECT name FROM employees WHERE name LIKE '%son';

-- Names containing 'ar'
SELECT name FROM employees WHERE name LIKE '%ar%';

-- Names with exactly 3 characters
SELECT name FROM employees WHERE name LIKE '___';

ILIKE - Case-Insensitive Matching

PostgreSQL has ILIKE for case-insensitive pattern matching:

-- These return the same results:
SELECT name FROM employees WHERE name ILIKE 'alice%';
SELECT name FROM employees WHERE name ILIKE 'ALICE%';

This is a PostgreSQL-specific feature - very useful!

Combining LIKE with Other Conditions

-- Engineers whose name starts with a vowel
SELECT name, department
FROM employees
WHERE department = 'Engineering'
  AND (name LIKE 'A%' OR name LIKE 'E%' OR name LIKE 'I%'
       OR name LIKE 'O%' OR name LIKE 'U%');

NOT LIKE

-- Employees whose email does NOT end with '@company.com'
SELECT name, email
FROM employees
WHERE email NOT LIKE '%@company.com';

Advanced: Regular Expressions

PostgreSQL supports full regular expressions with the ~ operator:

-- Names matching a regex pattern (case-sensitive)
SELECT name FROM employees WHERE name ~ '^[A-E]';

-- Case-insensitive regex
SELECT name FROM employees WHERE name ~* '^[a-e]';

Try It Yourself

1. Find employees whose name contains "son"
2. Find employees with emails starting with the letter "a"
3. Find employees in departments that start with "E" or "M"
4. Use ILIKE to find employees with "wil" anywhere in their name (case-insensitive)