Regular expression functions (regexp_match, regexp_replace) in PostgreSQL - Cheat Sheet & Quick Revision

Recall & Review

beginner

What does the PostgreSQL function regexp_match do?

It searches a string for a pattern using a regular expression and returns the first substring that matches as a text array.

beginner

How does regexp_replace work in PostgreSQL?

It searches a string for a pattern using a regular expression and replaces the first match (by default) with a specified replacement string. Use the 'g' flag to replace all occurrences.

intermediate

What is the return type of regexp_match in PostgreSQL?

It returns a text array containing the matched substring(s). If no match is found, it returns NULL.

intermediate

How can you replace only the first match of a pattern using regexp_replace?

regexp_replace replaces only the first match by default (no 'g' flag). You can also specify the occurrence parameter as 1.

beginner

Give an example of using regexp_match to extract the first word from a string.

Example: SELECT regexp_match('Hello world', '^\w+'); returns {Hello}.

What does regexp_match('abc123', '\\d+') return?

A '123'

B {'abc'}

C NULL

D {'123'}

The pattern '\d+' matches one or more digits. It returns a text array with the matched substring '123'.

Which function replaces text matching a pattern in PostgreSQL?

A regexp_replace

B replace

C substring

D regexp_match

regexp_replace replaces parts of a string matching a regex pattern.

What will regexp_replace('abc123abc', '\\d', 'X') return?

A abcX123abc

B abcXXXabc

C abcX23abc

D abcXabc

Without the 'g' flag, regexp_replace replaces only the first occurrence of '\d' (the first digit '1'), resulting in 'abcX23abc'.

If regexp_match finds no match, what does it return?

A Empty array

B NULL

C Empty string

D Error

When no match is found, regexp_match returns NULL.

How to replace only the first occurrence of a pattern using regexp_replace?

A Use 1 as occurrence parameter

B Use 'i' flag

C Use 'g' flag

D It always replaces all occurrences

Passing 1 as the occurrence parameter replaces only the first match.

Explain how regexp_match works in PostgreSQL and what it returns.

Think about how you find a pattern in a sentence and what you get back.

Describe how to use regexp_replace to change parts of a string matching a pattern.

Imagine correcting typos in a text by pattern.