REGEXP_MATCHES() function matches a POSIX regular expression against a string and returns the matching substrings.
The following illustrates the syntax of the PostgreSQL
REGEXP_MATCHES(source_string, pattern [, flags])
REGEXP_MATCHES() function accepts three arguments:
source is a string that you want to extract substrings which match a regular expression.
pattern is a POSIX regular expression for matching.
flags argument is one or more characters that control the behavior of the function. For example,
i allows you to match case-insensitively.
REGEXP_MATCHES() function returns a set of text, even if the result array only contains a single element.
Suppose, you have a social networking’s post as follows:
'Learning #PostgreSQL #REGEXP_MATCHES'
The following statement allows you to extract the hashtags such as
REGEXP_MATCHES('Learning #PostgreSQL #REGEXP_MATCHES',
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 (
g flags argument is for the global search.
The following is the result:
The result set has two rows, each is an array , which indicated that there were 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');
The result is:
In this tutorial, you have learned how to use the PostgreSQL
REGEXP_MATCHES() function to extract text according to a regular expression.