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.