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.