Regular expression matching (~ operator) in PostgreSQL - Interactive Code Practice

Practice - 5 Tasks

1fill in blank

easy

Complete the code to select rows where the column 'name' matches the pattern '^[A-Z]'.

PostgreSQL

SELECT * FROM users WHERE name [1] '^[A-Z]';

Drag options to blanks, or click blank then click option'

ALIKE

B=

CILIKE

D~

Attempts:

3 left

💡 Hint

Common Mistakes

Using LIKE instead of ~ for regex matching.

Using = which checks exact match, not pattern.

Using ILIKE which is case-insensitive LIKE, not regex.

The ~ operator in PostgreSQL is used for regular expression matching. Here, it checks if 'name' starts with an uppercase letter.

2fill in blank

medium

Complete the code to find rows where 'email' contains digits using regex.

PostgreSQL

SELECT * FROM contacts WHERE email [1] '\\d+';

Drag options to blanks, or click blank then click option'

ANOT ~

B!~

C~

DLIKE

Attempts:

3 left

💡 Hint

Common Mistakes

Using NOT ~ which negates the match.

Using !~ which means does not match.

Using LIKE which is not regex.

The ~ operator matches the regex pattern. Here, '\\d+' matches one or more digits in the email.

3fill in blank

hard

Fix the error in the query to select rows where 'phone' does not start with '+1'.

PostgreSQL

SELECT * FROM directory WHERE phone [1] '^\\+1';

Drag options to blanks, or click blank then click option'

A!~

B~

CLIKE

DNOT LIKE

Attempts:

3 left

💡 Hint

Common Mistakes

Using ~ which matches instead of negating.

Using LIKE which is not regex.

Using NOT LIKE which is not regex negation.

The !~ operator returns true if the regex does NOT match. Here, it filters phones not starting with '+1'.

4fill in blank

hard

Fill in the blank to select rows where 'username' ends with digits and is case-insensitive.

PostgreSQL

SELECT * FROM users WHERE username [1] '\\d+$';

Drag options to blanks, or click blank then click option'

AILIKE

B~*

C~

D!~

Attempts:

3 left

💡 Hint

Common Mistakes

Using ~ which is case-sensitive.

Using ILIKE which is not regex.

Using !~ which negates the match.

The ~* operator performs case-insensitive regex matching. The pattern '\\d+$' matches digits at the end.

5fill in blank

hard

Fill both blanks to select rows where 'address' contains either 'Street' or 'Avenue' using regex.

PostgreSQL

SELECT * FROM locations WHERE address [1] '[2]';

Drag options to blanks, or click blank then click option'

A~

BStreet|Avenue

CAND

D;

Attempts:

3 left

💡 Hint

Common Mistakes

Using AND instead of semicolon at the end.

Using LIKE instead of regex operator.

Incorrect regex pattern without alternation.

The ~ operator matches regex. The pattern 'Street|Avenue' matches either word. The semicolon ends the query.

Practice

1. What does the PostgreSQL operator ~ do when used in a query like column_name ~ 'pattern'?

easy

A. Returns the length of the column value

B. Replaces the column value with the pattern

C. Checks if the column value matches the regular expression pattern

D. Counts the number of times the pattern appears in the column

Solution

1. Step 1: Understand the ~ operator

   The ~ operator in PostgreSQL is used to check if a string matches a regular expression pattern.

2. Step 2: Apply to the query context

   When used as column_name ~ 'pattern', it returns true if the column's value matches the pattern, otherwise false.

3. Final Answer:

   Checks if the column value matches the regular expression pattern -> Option C

4. Quick Check:

   ~ means match pattern [OK]

Hint: Remember: ~ means 'matches pattern' in PostgreSQL [OK]

Common Mistakes:

• Thinking ~ replaces or modifies data
• Confusing ~ with counting occurrences
• Assuming ~ returns string length

2. Which of the following is the correct syntax to find rows where the column email contains the domain example.com using the ~ operator?

easy

A. SELECT * FROM users WHERE email ~ 'example.com';

B. SELECT * FROM users WHERE email ~ '@example\.com$';

C. SELECT * FROM users WHERE email LIKE '%example.com%';

D. SELECT * FROM users WHERE email = '~example.com';

Solution

1. Step 1: Understand the pattern for matching domain

   To match emails ending with '@example.com', the regex should be @example\.com$ where \. escapes the literal dot and $ means end of string.

2. Step 2: Check the syntax of the ~ operator usage

   The correct syntax is email ~ '@example\.com$' to find emails ending with that domain.

3. Final Answer:

   SELECT * FROM users WHERE email ~ '@example\.com$'; -> Option B

4. Quick Check:

   Regex match with ~ and end anchor [OK]

Hint: Use $ to anchor regex at string end with ~ operator [OK]

Common Mistakes:

• Using LIKE instead of ~ for regex matching
• Missing anchors like $ for exact domain match
• Using = operator with regex pattern

3. Given the table products with a column code containing values: 'A123', 'B234', 'A345', 'C456', what will the query SELECT code FROM products WHERE code ~ '^A'; return?

medium

A. A123, A345

B. B234, C456

C. All codes

D. No rows

Solution

1. Step 1: Understand the regex pattern ^A

   The caret ^ means the string must start with 'A'.

2. Step 2: Apply pattern to each code

   Codes starting with 'A' are 'A123' and 'A345'.

3. Final Answer:

   A123, A345 -> Option A

4. Quick Check:

   ^ means start with 'A' [OK]

Hint: ^ anchors regex to start of string [OK]

Common Mistakes:

• Confusing ^ with end anchor $
• Expecting all rows to match
• Ignoring regex anchors

4. You wrote the query SELECT * FROM users WHERE username ~ '*'; but it returns an error. What is the most likely cause?

medium

A. The regex pattern is missing escape characters for special symbols

B. The ~ operator requires the pattern to be enclosed in double quotes

C. The regex pattern is invalid because it lacks anchors

D. The pattern is correct but the column contains NULL values causing error

Solution

1. Step 1: Check regex pattern syntax

   The pattern * is invalid because the * quantifier requires a preceding atom (e.g., a character or group).

2. Step 2: Identify common error with special characters

   Regex special symbols like *, +, ? need escape characters (e.g., \* in SQL string) when used literally.

3. Final Answer:

   The regex pattern is missing escape characters for special symbols -> Option A

4. Quick Check:

   Escape special regex chars in SQL strings [OK]

Hint: Escape special regex characters properly in SQL strings [OK]

Common Mistakes:

• Using single quotes incorrectly for regex
• Assuming ~ needs double quotes
• Ignoring NULL values causing errors

5. You want to select all rows from orders where the order_id starts with a letter followed by exactly three digits. Which query using the ~ operator is correct?

hard

A. SELECT * FROM orders WHERE order_id ~ '^\w{4}$';

B. SELECT * FROM orders WHERE order_id ~ '^[0-9]{3}[A-Za-z]$';

C. SELECT * FROM orders WHERE order_id ~ '[A-Za-z][0-9]{3}';

D. SELECT * FROM orders WHERE order_id ~ '^[A-Za-z][0-9]{3}$';

Solution

1. Step 1: Understand the pattern requirements

   The order_id must start with a letter [A-Za-z] followed by exactly three digits [0-9]{3}, and nothing else.

2. Step 2: Check regex anchors and pattern

   Using ^ and $ anchors ensures the entire string matches. '^[A-Za-z][0-9]{3}$' matches a letter then three digits exactly.

3. Final Answer:

   SELECT * FROM orders WHERE order_id ~ '^[A-Za-z][0-9]{3}$'; -> Option D

4. Quick Check:

   ^ and $ anchor full string match [OK]

Hint: Use ^ and $ to match entire string pattern [OK]

Common Mistakes:

• Swapping letter and digit order
• Missing anchors causing partial matches
• Using \w which includes digits and letters