PostgreSQL – REGEXP_MATCHES Function
The REGEXP_MATCHES function is used to perform regular expression pattern matching on a given string. It returns all substrings in the input string that match a specified regular expression pattern. This function is useful when you need to extract specific pieces of information from a text based on a pattern.
The syntax of the REGEXP_MATCHES function is as follows:
REGEXP_MATCHES(string, pattern, flags)where string is the input string that you want to search for matches.
and pattern is the regular expression pattern that defines what you’re searching for.
and flags (optional): Flags that modify the behavior of the regular expression. These can include 'g' (global) for multiple matches, 'i' (case-insensitive), and others.
Here’s an example of how you might use the REGEXP_MATCHES function in a SQL query:
SELECT REGEXP_MATCHES('Email: john@example.com, Phone: 123-456-7890', '[a-zA-Z0-9._%+-]+@[a-zA-Z0-9.-]+\.[a-zA-Z]{2,4}', 'g') AS matches;In this example, the result would be an array of matched email addresses found in the input string. The regular expression pattern [a-zA-Z0-9._%+-]+@[a-zA-Z0-9.-]+\.[a-zA-Z]{2,4} matches common email addresses.
The REGEXP_MATCHES function returns an array of arrays, where each inner array corresponds to a set of capturing groups defined in the regular expression pattern. The first element of each inner array is the whole matched substring, and subsequent elements correspond to capturing groups.
The REGEXP_MATCHES function is especially helpful when you need to extract structured data from unstructured text.