regexp_extract, regexp_extract_all (string function)

Extracts the first regular expression match or all matches from a string.

Syntax

regexp_extract(<string>, regex => <regex> [, index => <index>])
regexp_extract_all(<string>, regex => <regex> [, index => <index>])

Arguments

Argument	Type	Description
`<string>`	`string`	String to search.
`regex`	`string`	Constant. Regular expression. Must be valid and non-NULL.
`index`	`int`	Optional constant; defaults to 0. Non-negative capture group index; 0 selects the whole match. Must not be NULL.

Returns

regexp_extract returns string, or NULL if there is no match or the selected group does not participate.

regexp_extract_all returns array, with one selected group per match. It returns [] if there are no matches; a missing group contributes a NULL element.

Both functions return NULL if the input string is NULL.

Examples

Extract fields from an event message

Pull the order ID and duration out of a checkout message, then cast the duration so it can be analyzed numerically.

VALUES
    ('order=o-17 status=paid duration_ms=245'),
    ('order=o-42 status=failed duration_ms=1800')
SELECT $0 AS message
SELECT
    regexp_extract(message, regex => 'order=(o-[0-9]+)', index => 1) AS order_id,
    regexp_extract(message, regex => 'duration_ms=([0-9]+)', index => 1)::int AS duration_ms

+----------+-------------+
| order_id | duration_ms |
+----------+-------------+
| o-17     | 245         |
| o-42     | 1800        |
+----------+-------------+

Capture group 1 selects the value without its field-name prefix.

Collect every referenced order

A support note can refer to several orders. Return all order IDs in their text order, including an empty array when there are no IDs.

VALUES
    ('case-17', 'Refund o-17 and o-42 after duplicate checkout'),
    ('case-18', 'Customer asked about opening hours')
SELECT $0 AS case_id, $1 AS note
SELECT case_id, regexp_extract_all(note, regex => 'o-[0-9]+') AS order_ids

+---------+-----------------+
| case_id | order_ids       |
+---------+-----------------+
| case-17 | ["o-17","o-42"] |
| case-18 | []              |
+---------+-----------------+