SIMILAR TO for regex-lite matching in PostgreSQL - Practice Problems & Coding Challenges

Challenge - 5 Problems

❓ query_result

intermediate

Basic SIMILAR TO pattern matching

Given a table products with a column name, which query returns all products whose names start with 'A' followed by any two characters and then 'e'?

PostgreSQL

SELECT name FROM products WHERE name SIMILAR TO 'A__e%';

A SELECT name FROM products WHERE name SIMILAR TO 'A__e%';

B SELECT name FROM products WHERE name SIMILAR TO 'A__e';

C SELECT name FROM products WHERE name SIMILAR TO 'A%e';

D SELECT name FROM products WHERE name SIMILAR TO 'A_e%';

💡 Hint

Remember that underscore (_) matches exactly one character in SIMILAR TO patterns.

The pattern 'A__e%' matches strings starting with 'A', followed by exactly two characters (each underscore is one character), then 'e', and then any sequence of characters (due to %). Option A correctly uses this pattern.

❓ query_result

intermediate

Using character classes in SIMILAR TO

Which query returns rows from users where the username starts with a digit between 1 and 5, followed by any characters?

PostgreSQL

SELECT username FROM users WHERE username SIMILAR TO '[1-5]%';

A SELECT username FROM users WHERE username SIMILAR TO '[1-5]%_';

B SELECT username FROM users WHERE username SIMILAR TO '[1-5_]';

C SELECT username FROM users WHERE username SIMILAR TO '[1-5]%';

D SELECT username FROM users WHERE username SIMILAR TO '[1-5]_%';

💡 Hint

The percent sign (%) matches zero or more characters after the initial pattern.

Option C uses '[1-5]%' which matches any string starting with a digit from 1 to 5, followed by zero or more characters. Other options either restrict length incorrectly or add unnecessary underscores.

📝 Syntax

advanced

Identify the syntax error in SIMILAR TO pattern

Which option contains a syntax error in the SIMILAR TO pattern?

PostgreSQL

SELECT * FROM orders WHERE order_code SIMILAR TO 'AB[0-9]{3}';

A SELECT * FROM orders WHERE order_code SIMILAR TO 'AB[0-9]{,3}';

B SELECT * FROM orders WHERE order_code SIMILAR TO 'AB[0-9][0-9][0-9]';

C SELECT * FROM orders WHERE order_code SIMILAR TO 'AB[0-9]{3}';

D ;'}3{]9-0[BA' OT RALIMIS edoc_redro EREHW sredro MORF * TCELES

💡 Hint

Check the syntax for repetition counts in SIMILAR TO patterns.

SIMILAR TO supports repetition counts like {3} or {3,} but not {,3}. Option A uses '{,3}' which is invalid syntax and causes an error.

❓ optimization

advanced

Optimizing SIMILAR TO for performance

Which query is likely to perform better when filtering email addresses starting with 'user' followed by any characters?

A SELECT email FROM contacts WHERE email SIMILAR TO 'user%';

B SELECT email FROM contacts WHERE email LIKE 'user%';

C SELECT email FROM contacts WHERE email SIMILAR TO 'user_+';

D SELECT email FROM contacts WHERE email SIMILAR TO 'user.*';

💡 Hint

Consider which operator uses simpler pattern matching and can use indexes.

The LIKE operator with 'user%' is simpler and can use indexes efficiently. SIMILAR TO uses regex-like patterns which are slower and less index-friendly. Option B is best for performance.

🧠 Conceptual

expert

Understanding SIMILAR TO pattern equivalence

Which SIMILAR TO pattern matches exactly the same set of strings as the regular expression ^cat|dog$?

A 'cat|dog'

B 'god|tac'

C cat|dog'

D '(cat|dog)'

💡 Hint

SIMILAR TO requires grouping parentheses to apply alternation correctly.

In SIMILAR TO, alternation '|' applies to the whole pattern unless grouped. '(cat|dog)' matches either 'cat' or 'dog' exactly. Without parentheses, 'cat|dog' matches strings that start with 'cat' or end with 'dog', not exact matches.

Practice

1. Which of the following best describes the SIMILAR TO operator in PostgreSQL?

easy

A. It matches strings using simple patterns with % and _ wildcards and some regex features.

B. It performs exact string matching without any pattern support.

C. It is used to compare numeric values with tolerance.

D. It matches strings only using full regular expressions.

Solution

1. Step 1: Understand SIMILAR TO purpose

   The SIMILAR TO operator matches strings using patterns that include % (any sequence) and _ (any single character), plus some regex-like features such as + and |.

2. Step 2: Compare options

   It matches strings using simple patterns with % and _ wildcards and some regex features. correctly describes this behavior. Options B, C, and D describe other unrelated behaviors.

3. Final Answer:

   It matches strings using simple patterns with % and _ wildcards and some regex features. -> Option A

4. Quick Check:

   SIMILAR TO = simple pattern matching [OK]

Hint: SIMILAR TO uses % and _ plus simple regex features [OK]

Common Mistakes:

• Confusing SIMILAR TO with full regex matching (~ operator)
• Thinking SIMILAR TO matches exact strings only
• Assuming SIMILAR TO works like numeric comparison

2. Which of the following is the correct syntax to check if column name matches a pattern starting with 'A' followed by any characters using SIMILAR TO?

easy

A. name SIMILAR TO 'A_'

B. name SIMILAR TO '%A'

C. name SIMILAR TO '_A%'

D. name SIMILAR TO 'A%'

Solution

1. Step 1: Understand pattern meaning

   The pattern 'A%' means the string starts with 'A' followed by any sequence of characters.

2. Step 2: Match pattern to options

   name SIMILAR TO 'A%' uses 'A%' which matches strings starting with 'A'. name SIMILAR TO '%A' matches strings ending with 'A'. name SIMILAR TO '_A%' requires second character 'A'. name SIMILAR TO 'A_' matches strings starting with 'A' and exactly one more character.

3. Final Answer:

   name SIMILAR TO 'A%' -> Option D

4. Quick Check:

   Start with 'A' = 'A%' [OK]

Hint: Use % after 'A' to match any characters after 'A' [OK]

Common Mistakes:

• Using % before 'A' to match start
• Confusing _ with %
• Using wrong pattern length

3. Given the table users(name TEXT) with rows: 'Anna', 'Anny', 'Andy', 'Any', which query returns only names starting with 'An' and ending with 'y' using SIMILAR TO?

medium

A. SELECT name FROM users WHERE name SIMILAR TO 'An%y';

B. SELECT name FROM users WHERE name SIMILAR TO 'An_y';

C. SELECT name FROM users WHERE name SIMILAR TO 'An_y%';

D. SELECT name FROM users WHERE name SIMILAR TO 'An_y_';

Solution

1. Step 1: Analyze pattern requirements

   We want names starting with 'An' and ending with 'y'. The middle can be any number of characters (including zero).

2. Step 2: Check each pattern

   SELECT name FROM users WHERE name SIMILAR TO 'An_y'; 'An_y' matches exactly 4 characters: 'An' + any single char + 'y' (matches 'Anny', 'Andy'; not 'Any'). SELECT name FROM users WHERE name SIMILAR TO 'An%y'; 'An%y' matches starting 'An', any characters in middle, ending with 'y' (matches 'Anny', 'Andy', 'Any'). SELECT name FROM users WHERE name SIMILAR TO 'An_y%'; 'An_y%' requires 4 characters minimum ('An' + single + 'y' + any after), matches 'Anny', 'Andy' (not 'Any'). SELECT name FROM users WHERE name SIMILAR TO 'An_y_'; 'An_y_' requires exactly 5 characters, no match.

3. Final Answer:

   SELECT name FROM users WHERE name SIMILAR TO 'An%y'; -> Option A

4. Quick Check:

   Start 'An', end 'y' = 'An%y' [OK]

Hint: Use % to match any length between start and end [OK]

Common Mistakes:

• Using _ instead of % for variable length
• Forgetting to include ending 'y' in pattern
• Assuming _ matches multiple characters

4. Consider this query: SELECT * FROM products WHERE code SIMILAR TO '[A-Z]+[0-9]'. It returns no rows even though some codes look like 'AB12'. What is the likely problem?

medium

A. The pattern matches only codes with exactly two characters.

B. SIMILAR TO does not support character classes like [A-Z] and [0-9].

C. The pattern should use % and _ instead of + for repetition.

D. The query syntax is invalid and causes an error.

Solution

1. Step 1: Understand SIMILAR TO pattern syntax

   SIMILAR TO supports some regex features but uses % for any sequence and _ for single character. The + operator is supported but character classes like [A-Z] are not fully supported as in full regex.

2. Step 2: Identify correct pattern usage

   Using [A-Z]+ is not valid in SIMILAR TO. Instead, use something like '(A|B|C|...)+'. Also, to match digits, use '[0-9]' carefully or replace with ranges or alternatives. The pattern should be rewritten using % and _ or explicit alternatives.

3. Final Answer:

   The pattern should use % and _ instead of + for repetition. -> Option C

4. Quick Check:

   SIMILAR TO needs %/_ for repetition, not + with classes [OK]

Hint: Use % and _ wildcards, not full regex classes [OK]

Common Mistakes:

• Assuming SIMILAR TO supports full regex syntax
• Using + without proper grouping
• Expecting character classes to work like full regex

5. You want to find all usernames in table users that start with either 'admin' or 'user' followed by exactly 3 digits. Which SIMILAR TO pattern correctly matches this?

hard

A. username SIMILAR TO '(admin|user)[0-9]{3}'

B. username SIMILAR TO '(admin|user)[0-9][0-9][0-9]'

C. username SIMILAR TO '(admin|user)%[0-9][0-9][0-9]'

D. username SIMILAR TO '(admin|user)_[0-9]{3}'

Solution

1. Step 1: Understand SIMILAR TO limitations

   SIMILAR TO supports alternation (|) and grouping with parentheses. However, it does not support {n} repetition syntax like full regex.

2. Step 2: Match exactly 3 digits

   To match exactly 3 digits, write [0-9][0-9][0-9] explicitly instead of [0-9]{3}.

3. Step 3: Check options

   username SIMILAR TO '(admin|user)[0-9]{3}' uses {3} which is invalid in SIMILAR TO. username SIMILAR TO '(admin|user)[0-9][0-9][0-9]' uses explicit three digit classes, correct. username SIMILAR TO '(admin|user)%[0-9][0-9][0-9]' uses % which matches any sequence, so it allows extra characters between prefix and digits, incorrect. username SIMILAR TO '(admin|user)_[0-9]{3}' uses _ which matches one character, but {3} is invalid.

4. Final Answer:

   username SIMILAR TO '(admin|user)[0-9][0-9][0-9]' -> Option B

5. Quick Check:

   Use explicit digit repeats, no {n} in SIMILAR TO [OK]

Hint: Write repeated patterns explicitly; no {n} in SIMILAR TO [OK]

Common Mistakes:

• Using {n} repetition syntax
• Using % wildcard to match exact length
• Confusing _ with digit matching