Pattern matching with LIKE and ILIKE in PostgreSQL - Cheat Sheet & Quick Revision

Recall & Review

beginner

What does the LIKE operator do in SQL?

The LIKE operator is used to search for a specified pattern in a column. It matches text based on wildcard characters like % for any sequence of characters and _ for a single character.

beginner

What is the difference between LIKE and ILIKE in PostgreSQL?

LIKE is case-sensitive, meaning it distinguishes uppercase and lowercase letters. ILIKE is case-insensitive, so it matches letters regardless of case.

beginner

What does the wildcard % represent in a LIKE pattern?

The % wildcard matches zero or more characters of any kind. For example, 'a%' matches any string starting with 'a'.

beginner

How do you match exactly one character in a pattern using LIKE?

Use the underscore _ wildcard to match exactly one character. For example, 'b_t' matches 'bat', 'bit', or 'but'.

intermediate

Write a query to find all names starting with 'Jo' ignoring case using ILIKE.

Example query: <br>SELECT * FROM users WHERE name ILIKE 'Jo%'; <br>This finds names like 'John', 'joanna', or 'JOSEPH'.

Which wildcard matches any number of characters in a LIKE pattern?

A %

B _

C *

D ?

The percent sign (%) matches zero or more characters in SQL pattern matching.

What does ILIKE do differently than LIKE in PostgreSQL?

A Matches patterns case-insensitively

B Matches patterns only at the start of the string

C Matches patterns only at the end of the string

D Matches patterns with regular expressions

ILIKE performs case-insensitive pattern matching.

Which pattern matches any single character in LIKE?

A %

B ?

C _

D #

The underscore (_) matches exactly one character.

What will WHERE name LIKE 'A%' return?

A Names containing 'A' anywhere

B Names ending with 'A'

C Names exactly equal to 'A'

D Names starting with 'A'

The pattern 'A%' matches any string starting with 'A'.

Which query finds names like 'Sam', 'sam', or 'SAM'?

A SELECT * FROM users WHERE name LIKE 'Sam';

B SELECT * FROM users WHERE name ILIKE 'Sam';

C SELECT * FROM users WHERE name LIKE '%Sam%';

D SELECT * FROM users WHERE name = 'Sam';

ILIKE matches 'Sam' ignoring case.

Explain how to use LIKE and ILIKE for pattern matching in PostgreSQL.

Think about how you find words that start with or contain certain letters.

Describe the difference between the wildcards % and _ in SQL pattern matching.

One wildcard is for many characters, the other for just one.