Why Pattern matching with LIKE and ILIKE in PostgreSQL? - Purpose & Use Cases

The Big Idea

What if you could find any name pattern in seconds, no matter how it's typed?

The Scenario

Imagine you have a huge list of customer names in a spreadsheet. You want to find all customers whose names start with 'Jo' or contain 'ann'. Doing this by scanning each name manually is like searching for a needle in a haystack.

The Problem

Manually checking each name is slow and tiring. You might miss some names because of typos or different letter cases like 'JoHN' vs 'john'. It's easy to make mistakes and waste hours.

The Solution

Using pattern matching with LIKE and ILIKE in PostgreSQL lets you quickly search for names that fit a pattern. LIKE is case-sensitive, while ILIKE ignores case differences, making your search smarter and faster.

Before vs After

✗ Before

Check each name one by one in the list and write down matches.

✓ After

SELECT * FROM customers WHERE name LIKE 'Jo%';
SELECT * FROM customers WHERE name ILIKE '%ann%';

What It Enables

You can easily find all records matching flexible text patterns, even ignoring letter case, saving time and reducing errors.

Real Life Example

A company wants to send a special offer to all customers whose names start with 'Jo' or contain 'ann', regardless of uppercase or lowercase letters. Using ILIKE, they get the list instantly.

Key Takeaways

Manual searching is slow and error-prone.

LIKE and ILIKE let you find text patterns in database columns.

ILIKE helps when you want case-insensitive matching.

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