Pattern matching with LIKE and ILIKE in PostgreSQL - Mini Project: Build & Apply

Pattern Matching with LIKE and ILIKE in PostgreSQL

📖 Scenario: You are working with a small bookstore database. You want to find books based on parts of their titles or authors' names. Sometimes you want to match exactly, and sometimes you want to ignore case differences.

🎯 Goal: Build SQL queries using LIKE and ILIKE to find books by title or author with pattern matching.

📋 What You'll Learn

Create a table called books with columns id, title, and author.

Insert specific book records into the books table.

Write a query using LIKE to find books with titles containing 'adventure'.

Write a query using ILIKE to find books with authors containing 'smith' ignoring case.

💡 Why This Matters

🌍 Real World

Pattern matching is useful in searching databases for partial matches, such as finding products, names, or titles that contain certain words or letters.

💼 Career

Database developers and analysts often use LIKE and ILIKE to filter data based on text patterns, improving search features and reports.

1

Create the books table

Write a SQL statement to create a table called books with columns: id as integer primary key, title as text, and author as text.

PostgreSQL

-- Create the books table with id, title, and author columns
-- Your code here

Hint

Use CREATE TABLE books and define the columns with their types. Remember to set id as the primary key.

2

Insert book records

Insert these exact records into the books table: (1, 'The Great Adventure', 'John Smith'), (2, 'Mystery of the Old House', 'Anna Brown'), (3, 'Adventures in Coding', 'Sam Smith'), (4, 'Cooking 101', 'Mary Johnson').

PostgreSQL

CREATE TABLE books (
    id INTEGER PRIMARY KEY,
    title TEXT,
    author TEXT
);
-- Insert the four book records below
-- Your code here

Hint

Use a single INSERT INTO books (id, title, author) VALUES statement with all four records separated by commas.

3

Query books with titles containing 'adventure' using LIKE

Write a SQL query to select all columns from books where the title contains the word 'adventure' using the LIKE operator. Use lowercase 'adventure' exactly and the percent sign (%) wildcard before and after the word.

PostgreSQL

CREATE TABLE books (
    id INTEGER PRIMARY KEY,
    title TEXT,
    author TEXT
);

INSERT INTO books (id, title, author) VALUES
(1, 'The Great Adventure', 'John Smith'),
(2, 'Mystery of the Old House', 'Anna Brown'),
(3, 'Adventures in Coding', 'Sam Smith'),
(4, 'Cooking 101', 'Mary Johnson');

-- Select books where title LIKE '%adventure%'
-- Your code here

Hint

Use SELECT * FROM books WHERE title LIKE '%adventure%' to find titles containing 'adventure'. Remember LIKE is case sensitive.

4

Query books with authors containing 'smith' using ILIKE

Write a SQL query to select all columns from books where the author contains the word 'smith' ignoring case, using the ILIKE operator. Use the percent sign (%) wildcard before and after 'smith'.

PostgreSQL

CREATE TABLE books (
    id INTEGER PRIMARY KEY,
    title TEXT,
    author TEXT
);

INSERT INTO books (id, title, author) VALUES
(1, 'The Great Adventure', 'John Smith'),
(2, 'Mystery of the Old House', 'Anna Brown'),
(3, 'Adventures in Coding', 'Sam Smith'),
(4, 'Cooking 101', 'Mary Johnson');

SELECT * FROM books WHERE title LIKE '%adventure%';

-- Select books where author ILIKE '%smith%'
-- Your code here

Hint

Use ILIKE instead of LIKE to ignore case when searching authors.

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