The PostgreSQL REGEXP_MATCHES() function matches a POSIX regular expression against a string and returns the matching substrings.
Syntax
The following illustrates the syntax of the PostgreSQL REGEXP_MATCHES() function:
1 | REGEXP_MATCHES(source_string, pattern [, flags]) |
Arguments
The REGEXP_MATCHES() function accepts three arguments:
1) source
The source is a string that you want to extract substrings which 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:
1 | 'Learning #PostgreSQL #REGEXP_MATCHES' |
The following statement allows you to extract the hashtags such as PostgreSQL and REGEXP_MATCHES:
1 2 3 4 | SELECT REGEXP_MATCHES('Learning #PostgreSQL #REGEXP_MATCHES', '#([A-Za-z0-9_]+)', 'g'); |
In this example, the following regular expression:
1 | #([A-Za-z0-9_]+) |
matches any word that starts with the hash character (#) and is followed by any alphanumeric characters or underscore (_).
The g flags argument is for the global search.
The following is the result:
1 2 3 4 5 | regexp_matches ----------------- {PostgreSQL} {REGEX_MATCHES} (2 rows) |
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:
1 | SELECT REGEXP_MATCHES('ABC', '^(A)(..)$', 'g'); |
The result is:
1 2 3 4 | regexp_matches ---------------- {A,BC} (1 row) |
In this tutorial, you have learned how to use the PostgreSQL REGEXP_MATCHES() function to extract text according to a regular expression.