Regular expression functions (regexp_match, regexp_replace) in PostgreSQL - Interactive Code Practice

Practice - 5 Tasks

1fill in blank

easy

Complete the code to find the first match of the pattern 'cat' in the text column.

PostgreSQL

SELECT regexp_match(description, '[1]') FROM animals;

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

A'fish'

B'dog'

C'bird'

D'cat'

Attempts:

3 left

💡 Hint

Common Mistakes

Forgetting to put the pattern inside quotes.

Using a pattern that does not exist in the text.

The regexp_match function returns the first substring matching the given regular expression. Here, we want to find 'cat'.

2fill in blank

medium

Complete the code to replace all digits in the 'phone' column with the character 'X'.

PostgreSQL

SELECT regexp_replace(phone, '[1]', 'X', 'g') FROM contacts;

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

A'[a-z]'

B'\s'

C'\d'

D'[A-Z]'

Attempts:

3 left

💡 Hint

Common Mistakes

Using a pattern that matches letters instead of digits.

Forgetting the global flag to replace all matches.

The pattern '\d' matches any digit. The 'g' flag means replace all occurrences.

3fill in blank

hard

Fix the error in the code to extract the first word starting with 'a' from the 'text' column.

PostgreSQL

SELECT regexp_match(text, '[1]') FROM documents;

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

A'a*'

B'\ba\w*\b'

C'[a-z]+'

D'^a'

Attempts:

3 left

💡 Hint

Common Mistakes

Using a pattern that matches partial words or characters only.

Not using word boundaries, causing incorrect matches.

The pattern '\ba\w*\b' matches a whole word starting with 'a'. The '\b' marks word boundaries.

4fill in blank

hard

Fill both blanks to replace all whitespace characters with a single space in the 'notes' column.

PostgreSQL

SELECT regexp_replace(notes, '[1]', '[2]', 'g') FROM logs;

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

A'\s+'

B' '

C'\d+'

D'_'

Attempts:

3 left

💡 Hint

Common Mistakes

Replacing digits or underscores instead of whitespace.

Not using the global flag to replace all occurrences.

The pattern '\s+' matches one or more whitespace characters. Replacing them with a single space ' ' cleans up the text.

5fill in blank

hard

Fill all three blanks to extract all words starting with a capital letter from the 'sentence' column.

PostgreSQL

SELECT regexp_match(sentence, '[1]', '[2]') FROM texts WHERE regexp_match(sentence, '[3]') IS NOT NULL;

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

A'\b[A-Z][a-z]*\b'

B'g'

C'i'

D'\d+'

Attempts:

3 left

💡 Hint

Common Mistakes

Using case-insensitive flag 'i' instead of global 'g'.

Using a pattern that matches digits instead of words.

The pattern '\b[A-Z][a-z]*\b' matches words starting with a capital letter. The 'g' flag extracts all matches. The WHERE clause filters rows having such words.