Skip to main content
Documents
Basic SyntaxMath FunctionsDate FunctionsJSON FunctionsDatabasesTables & Schema ManagementString FunctionsTriggersIndexes

PostgreSQL REGEXP_MATCHES

The REGEXP_MATCHES function in PostgreSQL is used to search for a pattern within a string using regular expressions and return all occurrences of matches. It is particularly useful for extracting substrings that match a given pattern.

Usage

The REGEXP_MATCHES function is used when you need to find and extract substrings that match a specified regular expression pattern from a string. It returns a set of text arrays, where each array contains the substrings matching the groups in the pattern. If the pattern matches but has no capturing groups, it returns an empty array.

SELECT REGEXP_MATCHES(source_string, pattern [, flags]);
  • source_string: The string to search.
  • pattern: The regular expression to match against the source string.
  • flags: Optional. Modifiers like 'i' for case-insensitivity. If omitted, the default behavior is case-sensitive matching.

Examples

1. Basic Pattern Match

SELECT REGEXP_MATCHES('abc123', '\d+');

This query searches for one or more digits in the string 'abc123' and returns {"123"} as the match.

2. Case-Insensitive Match

SELECT REGEXP_MATCHES('PostgreSQL', 'postgresql', 'i');

Here, the pattern 'postgresql' is matched case-insensitively against the string 'PostgreSQL', returning {"PostgreSQL"}.

3. Extracting Multiple Groups

SELECT REGEXP_MATCHES('User: John, Age: 30', 'User: (\w+), Age: (\d+)');

In this example, the function extracts the user name and age from the string, returning {"John", "30"} as the matched groups.

4. Using with LATERAL in a Complex Query

SELECT user_data.username, match
FROM users as user_data,
LATERAL REGEXP_MATCHES(user_data.details, 'User: (\w+), Age: (\d+)') AS match;

This example demonstrates using REGEXP_MATCHES in a FROM clause with LATERAL, extracting and displaying user details from a table.

Tips and Best Practices

  • Understand flag limitations. Note that the 'g' flag for global matching is not supported by REGEXP_MATCHES.
  • Capture specific parts. Use parentheses in patterns to capture specific parts of the matched string for detailed results.
  • Return type considerations. REGEXP_MATCHES returns a set of text arrays, which may require additional handling in your query logic.
  • Function selection. Compare with similar functions like REGEXP_REPLACE, REGEXP_SPLIT_TO_TABLE, and REGEXP_SPLIT_TO_ARRAY to choose the appropriate one for your needs.