Home - News ( United States | United Kingdom | Italy | Germany ) - Football scores

Showing content from https://www.geeksforgeeks.org/postgresql-regexp_matches-function/ below:

PostgreSQL - REGEXP_MATCHES Function - GeeksforGeeks

Last Updated : 15 Jul, 2025

The PostgreSQL REGEXP_MATCHES() function is a powerful tool for matching POSIX regular expressions against a string. It returns substrings that satisfy the pattern, making it indispensable for string manipulation, pattern matching, and data extraction tasks. In this article, we will explain the syntax, examples, and important considerations to help us master the REGEXP_MATCHES() function.

The REGEXP_MATCHES() function in PostgreSQL evaluates a given regular expression pattern against a string and returns matching substrings. It is particularly useful for tasks that require advanced text processing such as extracting, analyzing, or validating data based on specific patterns.

Unlike basic string-matching functions, REGEXP_MATCHES supports complex patterns and can return multiple matches, making it highly flexible for data extraction and manipulation.

Syntax

REGEXP_MATCHES(source_string, pattern [, flags])

Key Terms

• source_string: The string from which the regular expression will match and return the substrings.
  
• pattern: A POSIX regular expression used to match against the source string.
  
• flags (optional): Flags that modify the matching behavior. For example, 'g' (global) allows for multiple matches within the source string.

The REGEXP_MATCHES() function returns a set of text arrays containing substrings that match the given pattern.

Let us take a look at some of the examples of REGEXP_MATCHES Function in PostgreSQL to better understand the concept. These examples demonstrate how to use regular expressions effectively for various text-processing tasks.

This example demonstrates how to extract all hashtags from a text string, Using regular expressions to identify patterns starting with #. Suppose, we have a social networking’s post as follows

'Learning #Geeksforgeeks #geekPower'

The following statement allows us to extract the hashtags such as 'Geeksforgeeks' and 'geekPower':

SELECT 
    REGEXP_MATCHES('Learning #Geeksforgeeks #geekPower', '#([A-Za-z0-9_]+)', 'g');

Output

Explanation:

In this example, the regular expression '#([A-Za-z0-9_]+)' is used to match any hashtag that starts with a '#' followed by alphanumeric characters or underscores. The 'g' flag ensures that all matching patterns are returned.

We can use the REGEXP_MATCHES() function to match various patterns. For instance, to match the string 'ABC' with a pattern that identifies groups. This query demonstrates how grouping constructs in the pattern capture specific parts of the string, such as the first character and the remaining sequence.

Query:

SELECT REGEXP_MATCHES('ABC', '^(A)(..)$', 'g');

Output

Explanation:

In this case, the pattern ^(A)(..)$ captures the first character 'A' and the next two characters 'BC' as separate groups. The result groups are returned as an array.

• If there are multiple matches in the 'source_string', REGEXP_MATCHES will return a set of all matches.
  
• When the source string is NULL, REGEXP_MATCHES returns NULL and does not perform the regular expression matching.
  
• Be cautious with patterns that can match zero-length strings, such as '.*'. These can lead to infinite loops or unexpected results if not handled properly.
  
• The g flag (global) is often misunderstood. It does not change the function's behavior to find all matches in the way it does in other programming languages. Instead, REGEXP_MATCHES inherently returns all matches as a set.

The REGEXP_MATCHES() function in PostgreSQL is a flexible tool for extracting and analyzing data based on regular expressions. Its ability to handle multiple matches and return grouped results makes it ideal for tasks like pattern recognition, text mining, and data cleaning

RetroSearch is an open source project built by @garambo | Open a GitHub Issue

Search and Browse the WWW like it's 1997 | Search results from DuckDuckGo

HTML: 3.2 | Encoding: UTF-8 | Version: 0.7.4