PostgreSQL REGEXP_MATCHES Function

The PostgreSQL REGEXP_MATCHES() function matches a regular expression against a string and returns matched substrings.

Syntax

The following illustrates the syntax of the PostgreSQL REGEXP_MATCHES() function:

REGEXP_MATCHES(source_string, pattern [, flags])
Code language: CSS (css)

Arguments

The REGEXP_MATCHES() function accepts three arguments:

1) source

The source is a string that you want to extract substrings that match a regular expression.

2) pattern

The pattern is a POSIX regular expression for matching.

3) flags

The flags argument is one or more characters that control the behavior of the function. For example, i allows you to match case-insensitively.

Return Value

The REGEXP_MATCHES() function returns a set of text, even if the result array only contains a single element.

Examples

Suppose, you have a social networking’s post as follows:

'Learning #PostgreSQL #REGEXP_MATCHES'
Code language: JavaScript (javascript)

The following statement allows you to extract the hashtags such as PostgreSQL and REGEXP_MATCHES:

SELECT 
    REGEXP_MATCHES('Learning #PostgreSQL #REGEXP_MATCHES', 
         '#([A-Za-z0-9_]+)', 
        'g');
Code language: JavaScript (javascript)

In this example, the following regular expression matches any word that starts with the hash character (#) and is followed by any alphanumeric characters or underscore (_).

#([A-Za-z0-9_]+)
Code language: PHP (php)

The g flag argument is for the global search.

The following is the result:

regexp_matches
-----------------
 {PostgreSQL}
 {REGEX_MATCHES}
(2 rows)

The result set has two rows, each is an array, which indicates that there are two matches.

Noted that the REGEXP_MATCHES() returns each row as an array, rather than a string. Because if you use groups to capture parts of the text, the array will contain the groups as shown in the following example:

SELECT REGEXP_MATCHES('ABC', '^(A)(..)$', 'g');
Code language: JavaScript (javascript)

The result is:

regexp_matches
----------------
 {A,BC}
(1 row)

See the following film table from the sample database:

The following statement uses the REGEXP_MATCHES() function to get films whose descriptions contain the word Cat or Dog:

SELECT 
	film_id, 
	title , 
	description, 
	REGEXP_MATCHES(description, 'Cat | Dog ') cat_or_dog
FROM 
	film
ORDER BY title;	
Code language: JavaScript (javascript)

Here is the partial output:

In this tutorial, you have learned how to use the PostgreSQL REGEXP_MATCHES() function to extract text according to a regular expression.