Regular expression matching (~ operator) in PostgreSQL - Practice Problems & Coding Challenges

Challenge - 5 Problems

❓ query_result

intermediate

Find names starting with 'A' or 'a'

Given a table users with a column name, which query returns all users whose names start with the letter 'A' or 'a' using the regular expression match operator?

PostgreSQL

SELECT name FROM users WHERE name ~ '^[Aa]';

A SELECT name FROM users WHERE name ~* 'a';

B SELECT name FROM users WHERE name ~ '^a';

C SELECT name FROM users WHERE name ~ '^[Aa]';

D SELECT name FROM users WHERE name LIKE 'A%';

💡 Hint

Remember that ~ is case-sensitive and ^ means start of string.

Option C uses ~ with a regex that matches names starting with either uppercase or lowercase A. Option C uses ~* which is case-insensitive but without ^ matches 'a' anywhere. Option C uses LIKE which is not regex. Option C matches only lowercase 'a' at start.

📝 Syntax

intermediate

Identify the syntax error in regex query

Which of the following queries will cause a syntax error due to incorrect regular expression syntax?

A SELECT * FROM logs WHERE message ~ '[0-9]{3}-[0-9]{2}-[0-9]{4}';

B SELECT * FROM logs WHERE message ~ '^[A-Za-z]+$';

C SELECT * FROM logs WHERE message ~ '^Error.*$';

D SELECT * FROM logs WHERE message ~ '[0-9]{3-[0-9]{2}-[0-9]{4}';

💡 Hint

Check for balanced brackets in the regex pattern.

Option D has an unbalanced bracket in the regex pattern causing a syntax error. The others have valid regex patterns.

❓ optimization

advanced

Optimize regex for case-insensitive matching

You want to find all rows where the column title contains the word 'data' regardless of case. Which query is the most efficient and correct way to do this using PostgreSQL regex operators?

A SELECT * FROM books WHERE title ~* '\mdata\M';

B SELECT * FROM books WHERE title ~ '\mdata\M';

C SELECT * FROM books WHERE title ILIKE '%data%';

D SELECT * FROM books WHERE LOWER(title) ~ 'data';

💡 Hint

~* is the case-insensitive regex match operator.

Option A uses ~* for case-insensitive regex and word boundaries \m and \M to match whole word 'data'. Option A is case-sensitive. Option A uses ILIKE which is not regex. Option A converts to lowercase but uses case-sensitive regex.

🔧 Debug

advanced

Debug why regex query returns no rows

A user runs this query but gets no results: SELECT * FROM employees WHERE email ~ '^\w+@company\.com$'; The emails are stored in mixed case and look like '[e-mail]'. Why might this query return no rows?

A The regex is case-sensitive and emails might have uppercase letters.

B The ~ operator does not support regex matching.

C The regex pattern is missing anchors to match the whole string.

D The backslash before the dot is not escaped properly in SQL string.

💡 Hint

The ~ operator is case-sensitive.

The ~ operator performs case-sensitive regex matching. The pattern expects lowercase 'company' but emails have 'Company' with uppercase 'C', causing no matches. The other options are incorrect: ~ supports regex, anchors are present, and backslashes are properly handled.

🧠 Conceptual

expert

Understanding difference between ~ and ~* operators

Which statement best describes the difference between the PostgreSQL operators ~ and ~*?

A <code>~</code> matches regex anywhere in string; <code>~*</code> matches only at start.

B <code>~</code> performs case-sensitive regex matching; <code>~*</code> performs case-insensitive regex matching.

C <code>~</code> uses POSIX regex; <code>~*</code> uses Perl regex.

D <code>~</code> matches whole words only; <code>~*</code> matches partial words.

💡 Hint

Think about case sensitivity in regex matching.

The ~ operator matches regex patterns with case sensitivity. The ~* operator matches regex patterns ignoring case differences.