How to Use regexp_matches in PostgreSQL: Syntax and Examples

In PostgreSQL, use regexp_matches(string, pattern [, flags]) to find all substrings matching a regular expression pattern in a string. It returns a set of text arrays with each match. Use optional flags like 'g' for global matching to find multiple matches.

Syntax

The regexp_matches function has this syntax:

• string: The text to search.
• pattern: The regular expression pattern to match.
• flags (optional): Modifiers like 'g' for global matching.

It returns a set of text arrays, each array containing the matched substring(s).

sql

regexp_matches(string text, pattern text [, flags text]) RETURNS SETOF text[]

Example

This example finds all words starting with 'a' in a sentence using the global flag 'g' and case-insensitive flag 'i'.

sql

SELECT regexp_matches('An apple a day keeps the doctor away', '\ba\w*\b', 'gi');

Output

{An}
{apple}
{a}
{away}

Common Pitfalls

One common mistake is forgetting the 'g' flag, which causes regexp_matches to return only the first match instead of all matches.

Also, the function returns a set of arrays, so using it in a SELECT without proper handling can be confusing.

sql

/* Wrong: Missing 'g' flag, returns only first match */
SELECT regexp_matches('apple and apricot', 'a\w*');

/* Right: Using 'g' flag to get all matches */
SELECT regexp_matches('apple and apricot', 'a\w*', 'g');

Output

{apple}
{apple}
{apricot}

Quick Reference

Parameter	Description
string	Text to search for matches
pattern	Regular expression pattern to find
flags	Optional modifiers like 'g' for global, 'i' for case-insensitive
Return	Set of text arrays with matched substrings

Key Takeaways

Use regexp_matches to extract substrings matching a regex pattern from text in PostgreSQL.

Add the 'g' flag to find all matches, not just the first one.

regexp_matches returns a set of text arrays, so handle its output accordingly.

Patterns are regular expressions, so escape special characters properly.

Use case-insensitive flag 'i' to match letters regardless of case.