Regular expression functions (regexp_match, regexp_replace) in PostgreSQL - Practice Problems & Coding Challenges

Challenge - 5 Problems

❓ query_result

intermediate

What is the output of regexp_match with a capturing group?

Consider the following SQL query:

SELECT regexp_match('abc123xyz', '[a-z]+(\d+)[a-z]+');

What is the output of this query?

PostgreSQL

SELECT regexp_match('abc123xyz', '[a-z]+(\d+)[a-z]+');

A NULL

B {abc123xyz}

C {abc123xyz,123}

D {abc, 123, xyz}

💡 Hint

regexp_match returns the whole match followed by captured groups as a text array.

The pattern '[a-z]+(\d+)[a-z]+' matches letters, then captures digits, then letters. regexp_match returns the whole match followed by captured groups as an array. So it returns {abc123xyz,123}.

❓ query_result

intermediate

What does regexp_replace do with global flag?

Given the query:

SELECT regexp_replace('a1b2c3', '\d', 'X', 'g');

What is the output?

PostgreSQL

SELECT regexp_replace('a1b2c3', '\d', 'X', 'g');

A aXb2c3

B aXbXcX

C a1b2c3

D aXbXc3

💡 Hint

The 'g' flag means replace all matches, not just the first.

The pattern '\d' matches digits. With 'g' flag, all digits are replaced by 'X'. So 'a1b2c3' becomes 'aXbXcX'.

📝 Syntax

advanced

Which regexp_replace syntax is correct to replace only first digit?

Which of the following queries correctly replaces only the first digit in 'abc123' with 'Z'?

A SELECT regexp_replace('abc123', '\d', 'Z');

B SELECT regexp_replace('abc123', '\d', 'Z', 'g');

C SELECT regexp_replace('abc123', '\d+', 'Z', 'g');

D SELECT regexp_replace('abc123', '\d+', 'Z');

💡 Hint

Without 'g' flag, only the first match is replaced.

Option A replaces only the first digit because no 'g' flag is given. Option A replaces all digits. Option A replaces all digit groups. Option A replaces only the first digit group.

🧠 Conceptual

advanced

What error occurs with invalid regexp pattern?

What error will this query produce?

SELECT regexp_match('test', '[a-z+');

PostgreSQL

SELECT regexp_match('test', '[a-z+');

A syntax error in regular expression

B returns NULL

C empty array {}

D runtime error: invalid input syntax

💡 Hint

Unclosed bracket in regex causes syntax error.

The pattern '[a-z+' is invalid because the bracket is not closed. PostgreSQL raises a syntax error for invalid regex patterns.

❓ optimization

expert

Which regexp_replace is most efficient to remove all vowels?

You want to remove all vowels (a, e, i, o, u) from a large text column. Which query is most efficient?

A SELECT regexp_replace(text_column, '[aeiou]', '', 'g') FROM big_table;

B SELECT regexp_replace(text_column, '[aeiou]+', '', 'g') FROM big_table;

C SELECT replace(replace(replace(text_column, 'a', ''), 'e', ''), 'i', '') FROM big_table;

D SELECT translate(text_column, 'aeiou', '') FROM big_table;

💡 Hint

translate is faster than regexp_replace for single character replacements.

translate replaces characters directly without regex overhead, making it faster for removing vowels than regexp_replace or nested replace calls.

Practice

1. What does the PostgreSQL function regexp_match('abc123', '\\d+') return?

easy

A. An error because of incorrect syntax

B. The string 'abc123' unchanged

C. An empty array because no digits are found

D. An array with the first sequence of digits found: ['123']

Solution

1. Step 1: Understand regexp_match function

   The function searches the input string for the first substring matching the pattern.

2. Step 2: Apply pattern '\\d+' to 'abc123'

   The pattern '\\d+' matches one or more digits. In 'abc123', '123' matches.

3. Final Answer:

   An array with the first sequence of digits found: ['123'] -> Option D

4. Quick Check:

   regexp_match returns first match array [OK]

Hint: regexp_match returns first matching group as array [OK]

Common Mistakes:

• Thinking regexp_match returns the whole string
• Expecting a string instead of an array
• Confusing regexp_match with regexp_replace

2. Which of the following is the correct syntax to replace all digits in 'abc123def' with '#' using regexp_replace in PostgreSQL?

easy

A. regexp_replace('abc123def', '\\d+', '#', 'g')

B. regexp_replace('abc123def', '[0-9]', '#', 'g')

C. regexp_replace('abc123def', '[0-9]+', '#')

D. regexp_replace('abc123def', '\\d', '#')

Solution

1. Step 1: Understand regexp_replace parameters

   regexp_replace(text, pattern, replacement, flags) replaces matching parts. To replace all matches, use flag 'g'.

2. Step 2: Choose pattern and flags to replace all digits

   Pattern '\\d+' matches one or more digits. Flag 'g' ensures all digit sequences replaced, not just first.

3. Final Answer:

   regexp_replace('abc123def', '\\d+', '#', 'g') -> Option A

4. Quick Check:

   Use 'g' flag to replace all matches [OK]

Hint: Use 'g' flag in regexp_replace to replace all matches [OK]

Common Mistakes:

• Omitting 'g' flag replaces only first match
• Using '\\d' without 'g' replaces only one digit
• Confusing pattern syntax with wrong brackets

3. What is the result of this query?

SELECT regexp_match('user_2023_data', 'user_(\\d{4})_data');

medium

A. NULL

B. ['user_2023_data']

C. ['2023']

D. An error due to wrong pattern

Solution

1. Step 1: Analyze the pattern 'user_(\\d{4})_data'

   The pattern looks for 'user_' followed by exactly 4 digits (captured), then '_data'.

2. Step 2: Match pattern against 'user_2023_data'

   The string matches perfectly, capturing '2023' as the group.

3. Final Answer:

   ['2023'] -> Option C

4. Quick Check:

   Captured group returns ['2023'] [OK]

Hint: Captured groups in regexp_match return as array [OK]

Common Mistakes:

• Expecting full string instead of captured group
• Not escaping backslashes properly
• Confusing regexp_match with regexp_replace output

4. Identify the error in this query:

SELECT regexp_replace('abc123def', '\\d+', '#');

It is intended to replace all digit sequences with '#'.

medium

A. regexp_replace cannot replace digits

B. Missing 'g' flag, so only first digit sequence replaced

C. Replacement '#' must be in double quotes

D. Pattern '\\d+' is invalid syntax

Solution

1. Step 1: Understand regexp_replace behavior without flags

   Without 'g' flag, regexp_replace replaces only the first match.

2. Step 2: Check the pattern and replacement

   Pattern '\\d+' is valid and replacement '#' is correct. Only missing 'g' flag causes partial replacement.

3. Final Answer:

   Missing 'g' flag, so only first digit sequence replaced -> Option B

4. Quick Check:

   Use 'g' flag to replace all matches [OK]

Hint: Add 'g' flag to replace all matches in regexp_replace [OK]

Common Mistakes:

• Assuming all matches replaced without 'g'
• Thinking pattern syntax is wrong
• Using single quotes incorrectly for replacement

5. You want to extract the domain name from email addresses like '[e-mail]' using regexp_match. Which query correctly extracts 'example'?

hard

A. SELECT (regexp_match('[e-mail]', '@([\\w]+)\\.com'))[1];

B. SELECT regexp_match('[e-mail]', '@([\\w]+)\\.com');

C. SELECT regexp_replace('[e-mail]', '.*@([\\w]+)\\.com', '\\1');

D. SELECT regexp_replace('[e-mail]', '@([\\w]+)\\.com', '\\1');

Solution

1. Step 1: Understand regexp_match returns array of groups

   regexp_match returns an array with captured groups. To get the domain, access first element [1].

2. Step 2: Check pattern '@([\\w]+)\\.com'

   This pattern captures word characters after '@' and before '.com', which is the domain.

3. Final Answer:

   SELECT (regexp_match('[e-mail]', '@([\\w]+)\\.com'))[1]; -> Option A

4. Quick Check:

   Access first group from regexp_match array [OK]

Hint: Use (regexp_match(...))[1] to get captured group string [OK]

Common Mistakes:

• Not accessing array element from regexp_match
• Using regexp_replace without global flag incorrectly
• Confusing pattern syntax for domain extraction