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.