PostgreSQL - REGEXP_MATCHES Function

In PostgreSQL, the REGEXP_MATCHES function is used to search for substrings that match a POSIX regular expression pattern within a string. It returns all matches in the string as an array of text. If no matches are found, the function returns no rows.

Syntax:

REGEXP_MATCHES(string, pattern [, flags])

string: The source string in which you want to search for matches.
pattern: The POSIX regular expression pattern.
flags: Optional. It's a string consisting of one or more of the following characters that change the matching behavior:
- i: Makes the match case-insensitive.
- g: Returns all matches (global), not just the first one.
- m: Treats the source string as multiple lines. Causes ^ and $ to match the start/end of each line.
- n: Matches any newline character (\n, \r, etc.).
- p: Permits POSIX syntax (default).
- w: Ignores whitespace and allows comments within patterns.
- x: Extends syntax (similar to p but more liberal).

Examples:

Basic Usage:
This example retrieves the first sequence of digits from the given string:
```
SELECT REGEXP_MATCHES('The price is 123 and the code is 456', '\d+');
```
Result: {"123"}
Using the Global Flag:
To retrieve all sequences of digits:
```
SELECT REGEXP_MATCHES('The price is 123 and the code is 456', '\d+', 'g');
```
Result: {"123", "456"}
Case-insensitive Matching:
Find all occurrences of the word "hello", regardless of case:
```
SELECT REGEXP_MATCHES('Hello there! hello again!', 'hello', 'gi');
```
Result: {"Hello", "hello"}
Multi-line Matching:
Find occurrences of the word "start" at the beginning of a line:
```
SELECT REGEXP_MATCHES('start of text\nnot the start\nStart here.', '^start', 'mi');
```
Result: {"start", "Start"}

Notes:

The REGEXP_MATCHES function returns an array because, when used with the 'g' flag, there can be multiple matches in the source string.
If you're familiar with regular expressions in other programming languages, PostgreSQL's POSIX regular expressions will be quite similar but may have some differences in terms of syntax and supported features.

In conclusion, the REGEXP_MATCHES function in PostgreSQL offers a powerful way to search for patterns within strings, providing flexibility with various flags and supporting comprehensive POSIX regular expressions.

How to use REGEXP_MATCHES function in PostgreSQL:
- Use REGEXP_MATCHES to perform regular expression matching.
```
SELECT REGEXP_MATCHES('Hello, World!', 'Hello');
```
Regular expression matching with REGEXP_MATCHES in PostgreSQL:
- Match a pattern within a string using regular expressions.
```
SELECT REGEXP_MATCHES('123-456-7890', '\d{3}-\d{3}-\d{4}');
```
Extracting matched substrings with REGEXP_MATCHES:
- Extract specific substrings using capturing groups.
```
SELECT REGEXP_MATCHES('Date: 2022-01-01', 'Date: (\d{4}-\d{2}-\d{2})');
```
Using capturing groups with REGEXP_MATCHES in PostgreSQL:
- Utilize capturing groups for extracting specific parts of a match.
```
SELECT REGEXP_MATCHES('John Doe (30)', '(\w+) (\w+) $(\d+)$');
```
Using REGEXP_MATCHES with multiple patterns in PostgreSQL:
- Match against multiple patterns and retrieve results.
```
SELECT REGEXP_MATCHES('abc123', '(\d+)', 'g');
```
REGEXP_MATCHES and case sensitivity in PostgreSQL:
- Control case sensitivity in regular expression matching.
```
SELECT REGEXP_MATCHES('Hello, World!', 'hello', 'i');
```

SELECT UNNEST(REGEXP_MATCHES('apple, orange, banana', '\w+', 'g'));

SELECT * FROM your_table WHERE REGEXP_MATCHES(column_name, 'pattern');