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.

Practice

1. Which of the following statements about LIKE and ILIKE in PostgreSQL is true?

easy

A. LIKE is case-sensitive, while ILIKE is case-insensitive.

B. LIKE ignores case, but ILIKE is case-sensitive.

C. LIKE and ILIKE both ignore case.

D. ILIKE is used only for numeric pattern matching.

Solution

1. Step 1: Understand the difference between LIKE and ILIKE

   LIKE matches patterns considering case, so 'A' and 'a' are different. ILIKE ignores case differences.

2. Step 2: Evaluate the options

   LIKE is case-sensitive, while ILIKE is case-insensitive correctly states the case sensitivity difference. The other options are incorrect based on PostgreSQL behavior.

3. Final Answer:

   LIKE is case-sensitive, while ILIKE is case-insensitive. -> Option A

4. Quick Check:

   LIKE case-sensitive = A [OK]

Hint: Remember: LIKE cares about case, ILIKE ignores it [OK]

Common Mistakes:

• Confusing LIKE and ILIKE case sensitivity
• Thinking ILIKE works in all SQL databases
• Assuming LIKE ignores case by default

2. Which of the following is the correct syntax to find rows where the column name starts with 'Jo' using case-insensitive matching?

easy

A. SELECT * FROM users WHERE name LIKE 'Jo%';

B. SELECT * FROM users WHERE name LIKE '%Jo';

C. SELECT * FROM users WHERE name ILIKE 'Jo%';

D. SELECT * FROM users WHERE name ILIKE '%Jo';

Solution

1. Step 1: Identify the pattern for names starting with 'Jo'

   The pattern should be 'Jo%' to match strings starting with 'Jo'.

2. Step 2: Choose case-insensitive matching syntax

   Use ILIKE for case-insensitive matching in PostgreSQL. SELECT * FROM users WHERE name ILIKE 'Jo%'; uses ILIKE 'Jo%', which is correct.

3. Final Answer:

   SELECT * FROM users WHERE name ILIKE 'Jo%'; -> Option C

4. Quick Check:

   ILIKE + 'Jo%' = C [OK]

Hint: Use ILIKE with 'pattern%' to match start ignoring case [OK]

Common Mistakes:

• Using LIKE instead of ILIKE for case-insensitive search
• Using '%Jo' which matches end, not start
• Forgetting the % wildcard

3. Given a table products with a column code, what will the following query return?

SELECT code FROM products WHERE code LIKE '_A%';

Assuming the code values are: '1A23', 'BA45', 'CA67', 'AA89', 'AB12'.

medium

A. AA89, AB12

B. 1A23, AA89

C. BA45, CA67, AB12

D. 1A23, BA45, CA67, AA89

Solution

1. Step 1: Understand the pattern '_A%'

   The underscore (_) matches exactly one character, 'A' is literal, and % matches any sequence after. So the second character must be 'A'.

2. Step 2: Check each code value

   '1A23' (second char 'A') matches. 'BA45' (second 'A') matches. 'CA67' (second 'A') matches. 'AA89' (second 'A') matches. 'AB12' (second 'B') does not match.

3. Final Answer:

   1A23, BA45, CA67, AA89 -> Option D

4. Quick Check:

   Second char 'A' = D [OK]

Hint: Underscore matches one char; check second char carefully [OK]

Common Mistakes:

• Confusing _ with %
• Ignoring position of characters in pattern
• Missing some matching rows

4. You wrote this query to find all users whose email ends with '@example.com' ignoring case:

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

But it misses emails like '[e-mail]'. What is the fix?

medium

A. Change LIKE to ILIKE: email ILIKE '[e-mail]'

B. All of the above

C. Use UPPER function: UPPER(email) LIKE '[e-mail]'

D. Add LOWER function: LOWER(email) LIKE '[e-mail]'

Solution

1. Step 1: Identify why LIKE misses case differences

   LIKE is case-sensitive, so '[e-mail]' does not match '[e-mail]'.

2. Step 2: Explore fixes for case-insensitive matching

   Change LIKE to ILIKE: email ILIKE '[e-mail]' uses ILIKE which ignores case. Add LOWER function: LOWER(email) LIKE '[e-mail]' converts email to lowercase before matching. Use UPPER function: UPPER(email) LIKE '[e-mail]' converts email to uppercase and matches uppercase pattern. All achieve case-insensitive matching.

3. Step 3: Confirm all options work

   All three approaches fix the problem, so the correct answer is all of the above.

4. Final Answer:

   All of the above -> Option B

5. Quick Check:

   Case-insensitive fix = B [OK]

Hint: Use ILIKE or LOWER/UPPER for case-insensitive matching [OK]

Common Mistakes:

• Using LIKE without case conversion
• Forgetting to match case in pattern
• Assuming LIKE is case-insensitive

5. You want to find all product codes in a table inventory where the code contains exactly three letters followed by any digits, ignoring case. Which query correctly uses pattern matching?

hard

A. SELECT * FROM inventory WHERE code ~* '^[a-zA-Z]{3}[0-9]+$';

B. SELECT * FROM inventory WHERE code ILIKE '___%';

C. SELECT * FROM inventory WHERE code LIKE '___%';

D. SELECT * FROM inventory WHERE code ILIKE '???%';

Solution

1. Step 1: Understand the pattern requirements

   We need exactly three letters (any case) followed by digits, and the match should be case-insensitive.

2. Step 2: Evaluate pattern matching options

   LIKE '___%' and ILIKE '___%' match any three characters followed by anything, but do not restrict to letters followed by digits. ILIKE '???%' uses invalid wildcard syntax (? is not supported in SQL LIKE/ILIKE). The ~* '^[a-zA-Z]{3}[0-9]+$' uses regex with case-insensitive flag to match exactly three letters then one or more digits to end of string, which fits perfectly.

3. Step 3: Confirm correct syntax and behavior

   ~* is PostgreSQL's case-insensitive regex match operator with correct pattern.

4. Final Answer:

   SELECT * FROM inventory WHERE code ~* '^[a-zA-Z]{3}[0-9]+$'; -> Option A

5. Quick Check:

   Regex with ~* matches exact pattern = A [OK]

Hint: Use regex (~*) for exact letter-digit patterns ignoring case [OK]

Common Mistakes:

• Using LIKE with wildcards but no character restrictions
• Using invalid wildcard characters like '?'
• Not using case-insensitive matching properly