REGEXP_MATCHES в PostgreSQL: как доставать группы из строки

В SQL часто приходится работать со строками, внутри которых спрятано несколько разных частей.

Например:

order-4815

В этой строке есть текстовый префикс order и номер заказа 4815.

Или email:

alex@gmail.com

В нём есть локальная часть alex и домен gmail.com.

Или строка лога:

ERROR 2026-06-19 Payment failed

В ней можно выделить уровень ошибки, дату и сообщение.

Для таких задач в PostgreSQL есть функция REGEXP_MATCHES. Она ищет совпадение по регулярному выражению и возвращает найденные части в виде массива.

Простой пример:

SELECT REGEXP_MATCHES('order-4815', 'order-([0-9]+)');

Результат:

{4815}

Число 4815 попало в массив, потому что в шаблоне оно было захвачено в круглые скобки:

([0-9]+)

Вот ради таких случаев REGEXP_MATCHES и используют: когда из одной строки нужно достать одну или несколько смысловых частей.

Что делает REGEXP_MATCHES

REGEXP_MATCHES ищет в строке совпадение с регулярным выражением.

Синтаксис:

REGEXP_MATCHES(string, pattern [, flags])

Например:

SELECT REGEXP_MATCHES('user:42', 'user:([0-9]+)');

Результат:

{42}

Функция нашла строку user:42, а в результат вернула только то, что попало в группу захвата.

Группа захвата — это часть регулярного выражения в круглых скобках:

([0-9]+)

Здесь:

• [0-9] означает любую цифру;
• + означает «один или больше раз»;
• круглые скобки говорят: «запомни эту часть».

Поэтому из строки user:42 мы достали 42.

REGEXP_MATCHES возвращает массив

Главная особенность REGEXP_MATCHES: она возвращает не обычную строку, а массив text[].

Например:

SELECT REGEXP_MATCHES('alex@gmail.com', '^([^@]+)@(.+)$');

Результат:

{alex,gmail.com}

Почему два элемента?

Потому что в шаблоне две группы:

^([^@]+)@(.+)$

Первая группа:

([^@]+)

захватывает всё до символа @.

Вторая группа:

(.+)

захватывает всё после @.

Поэтому результатом стал массив:

{alex,gmail.com}

Первый элемент массива — alex.

Второй элемент массива — gmail.com.

Как достать конкретную группу

Чтобы взять конкретный элемент массива, используют индекс.

В PostgreSQL массивы начинаются с индекса 1, а не с 0.

SELECT (REGEXP_MATCHES('alex@gmail.com', '^([^@]+)@(.+)$'))[1] AS local_part;

Результат:

local_part
----------
alex

Чтобы получить домен:

SELECT (REGEXP_MATCHES('alex@gmail.com', '^([^@]+)@(.+)$'))[2] AS domain;

Результат:

domain
---------
gmail.com

Обратите внимание на скобки вокруг функции:

(REGEXP_MATCHES(...))[1]

Они нужны, чтобы PostgreSQL сначала получил массив, а потом взял из него первый элемент.

Без этих скобок выражение читается хуже и может работать не так, как вы ожидаете.

Пример с email в таблице

Допустим, есть таблица пользователей:

id | email
---+------------------
1  | alex@gmail.com
2  | maria@yahoo.com
3  | ivan@example.org

Нужно отдельно получить часть до @ и часть после @.

SELECT
    id,
    email,
    (REGEXP_MATCHES(email, '^([^@]+)@(.+)$'))[1] AS local_part,
    (REGEXP_MATCHES(email, '^([^@]+)@(.+)$'))[2] AS domain
FROM users;

Результат:

id | email            | local_part | domain
---+------------------+------------+-------------
1  | alex@gmail.com   | alex       | gmail.com
2  | maria@yahoo.com  | maria      | yahoo.com
3  | ivan@example.org | ivan       | example.org

На маленьком примере всё выглядит удобно.

Но у такого запроса есть две проблемы:

1. Функция вызывается два раза.
2. Если email не совпадёт с шаблоном, строка может исчезнуть из результата.

Вторая проблема особенно важная. Разберём её отдельно.

Главная ловушка: нет совпадения — нет строки

Обычно мы привыкли, что функция возвращает значение.

Например:

LOWER(name)

возвращает имя в нижнем регистре.

Если значение NULL, результат будет NULL.

Но REGEXP_MATCHES ведёт себя иначе. Это не обычная скалярная функция. Она возвращает набор строк.

Если совпадение есть — функция возвращает строку результата.

Если совпадения нет — функция возвращает ноль строк.

Из-за этого исходная строка таблицы может просто исчезнуть из результата.

Пример:

SELECT
    id,
    name,
    (REGEXP_MATCHES(name, '([0-9]+)'))[1] AS digits
FROM users;

Допустим, данные такие:

id | name
---+----------
1  | user-42
2  | admin
3  | client-105

Результат будет таким:

id | name       | digits
---+------------+--------
1  | user-42    | 42
3  | client-105 | 105

Куда делась строка admin?

Она исчезла, потому что в ней нет цифр. REGEXP_MATCHES(name, '([0-9]+)') не нашла совпадение и вернула ноль строк.

Это не NULL.

Это именно отсутствие строки в результате.

Для новичков это очень неожиданно.

Почему это опасно

Представьте, что вы делаете отчёт по пользователям и хотите просто достать цифры из поля name.

Вы ожидаете получить всех пользователей:

user-42      → 42
admin        → NULL
client-105   → 105

А на деле получаете только тех, у кого есть совпадение:

user-42      → 42
client-105   → 105

Строка без цифр пропала.

Ошибки SQL не будет. Запрос выполнится. Просто количество строк в отчёте станет меньше.

Именно поэтому REGEXP_MATCHES нужно использовать осторожно в списке SELECT.

Безопасный вариант через REGEXP_MATCH

Если нужно только первое совпадение, часто удобнее использовать REGEXP_MATCH без буквы S на конце.

SELECT REGEXP_MATCH('user-42', '([0-9]+)');

Результат:

{42}

Но если совпадения нет:

SELECT REGEXP_MATCH('admin', '([0-9]+)');

результат будет:

NULL

То есть REGEXP_MATCH возвращает массив или NULL.

А REGEXP_MATCHES возвращает строки: ноль строк, одну строку или несколько строк.

Разница очень важная:

REGEXP_MATCH   -> одно значение: массив или NULL
REGEXP_MATCHES -> набор строк

Если вам нужно сохранить все строки таблицы и достать только первое совпадение, чаще берите REGEXP_MATCH.

Пример:

SELECT
    id,
    name,
    (REGEXP_MATCH(name, '([0-9]+)'))[1] AS digits
FROM users;

Теперь строка без цифр не исчезнет. В колонке digits будет NULL.

Безопасный вариант через REGEXP_SUBSTR

В PostgreSQL 15+ есть функция REGEXP_SUBSTR.

Она возвращает найденную подстроку, а если совпадения нет — возвращает NULL.

Например:

SELECT
    id,
    name,
    REGEXP_SUBSTR(name, '[0-9]+') AS digits
FROM users;

Если данные такие:

id | name
---+----------
1  | user-42
2  | admin
3  | client-105

результат будет таким:

id | name       | digits
---+------------+--------
1  | user-42    | 42
2  | admin      | NULL
3  | client-105 | 105

Это часто именно то поведение, которое нужно в отчётах: строка остаётся, а отсутствующее совпадение показывается как NULL.

Если нужно вернуть не всё совпадение, а конкретную группу, у REGEXP_SUBSTR есть дополнительный аргумент subexpr.

Например, достанем домен из email:

SELECT
    id,
    email,
    REGEXP_SUBSTR(email, '@(.+)$', 1, 1, '', 1) AS domain
FROM users;

Что означают аргументы после шаблона:

• 1 — начать поиск с первого символа;
• 1 — взять первое совпадение;
• '' — без дополнительных флагов;
• 1 — вернуть первую группу из шаблона.

Для строки:

alex@gmail.com

результат будет:

gmail.com

Когда всё-таки нужен REGEXP_MATCHES

Если REGEXP_MATCH и REGEXP_SUBSTR безопаснее, зачем вообще нужен REGEXP_MATCHES?

Он нужен в двух случаях.

Первый случай: вы хотите получить группы из совпадения и осознанно работаете с функцией как с набором строк.

Второй случай: вам нужны все совпадения в строке, а не только первое.

И вот здесь появляется флаг g.

Флаг g: получить все совпадения

По умолчанию REGEXP_MATCHES возвращает только первое совпадение.

Например:

SELECT REGEXP_MATCHES('tag:sql tag:postgres tag:regex', 'tag:([a-z]+)');

Результат:

{sql}

Функция нашла только первое совпадение: tag:sql.

Чтобы получить все совпадения, нужно добавить третий аргумент — флаг g.

SELECT REGEXP_MATCHES('tag:sql tag:postgres tag:regex', 'tag:([a-z]+)', 'g');

Результат:

{sql}
{postgres}
{regex}

Теперь функция вернула три строки: по одной на каждое совпадение.

g означает global, то есть «искать по всей строке».

REGEXP_MATCHES с флагом g размножает строки

Это очень важный момент.

Если в одной строке три совпадения, REGEXP_MATCHES с флагом g вернёт три строки.

Например, есть таблица:

id | tags
---+--------------------------
1  | sql postgres regex
2  | mysql
3  | clickhouse sql

Запрос:

SELECT
    id,
    (REGEXP_MATCHES(tags, '([a-z]+)', 'g'))[1] AS tag
FROM articles;

Результат:

id | tag
---+------------
1  | sql
1  | postgres
1  | regex
2  | mysql
3  | clickhouse
3  | sql

Строка с id = 1 превратилась в три строки, потому что в ней три слова.

Это не ошибка. Это нормальное поведение REGEXP_MATCHES с флагом g.

Но если вы потом соединяете такой результат с другими таблицами или считаете суммы, нужно помнить: строки родительской таблицы могут задублироваться.

Как аккуратно использовать REGEXP_MATCHES через LATERAL

Более явный и понятный способ работать с REGEXP_MATCHES — использовать LATERAL.

Например:

SELECT
    a.id,
    m.match[1] AS tag
FROM articles a
CROSS JOIN LATERAL REGEXP_MATCHES(a.tags, '([a-z]+)', 'g') AS m(match);

Здесь происходит следующее:

1. PostgreSQL берёт строку из articles.
2. Для этой строки запускает REGEXP_MATCHES.
3. Каждое найденное совпадение приклеивает к исходной строке.
4. Если совпадений несколько, исходная строка повторяется несколько раз.

CROSS JOIN LATERAL честно показывает, что функция может вернуть несколько строк.

Это лучше, чем прятать REGEXP_MATCHES прямо в SELECT, где размножение строк выглядит неожиданно.

Как сохранить строки без совпадений

Если нужно сохранить даже те строки, где совпадений нет, используйте LEFT JOIN LATERAL.

Например:

SELECT
    u.id,
    u.name,
    m.match[1] AS digits
FROM users u
LEFT JOIN LATERAL REGEXP_MATCHES(u.name, '([0-9]+)') AS m(match)
    ON true;

Если данные такие:

id | name
---+----------
1  | user-42
2  | admin
3  | client-105

результат будет таким:

id | name       | digits
---+------------+--------
1  | user-42    | 42
2  | admin      | NULL
3  | client-105 | 105

Вот это уже безопасный вариант: строка admin не исчезла, просто в digits стоит NULL.

Запомните:

CROSS JOIN LATERAL -> оставить только строки с совпадениями
LEFT JOIN LATERAL  -> сохранить все строки, даже без совпадений

Пример: достать номер заказа

Допустим, есть таблица заказов:

id | code
---+------------
1  | order-4815
2  | order-9021
3  | draft

Нужно достать номер из строк вида order-4815.

Если нужны только строки, где номер есть:

SELECT
    id,
    code,
    (REGEXP_MATCHES(code, '^order-([0-9]+)$'))[1] AS order_no
FROM orders;

Результат:

id | code       | order_no
---+------------+---------
1  | order-4815 | 4815
2  | order-9021 | 9021

Строка draft исчезла.

Если это нормально — запрос подходит.

Если нужно сохранить все строки:

SELECT
    o.id,
    o.code,
    m.match[1] AS order_no
FROM orders o
LEFT JOIN LATERAL REGEXP_MATCHES(o.code, '^order-([0-9]+)$') AS m(match)
    ON true;

Результат:

id | code       | order_no
---+------------+---------
1  | order-4815 | 4815
2  | order-9021 | 9021
3  | draft      | NULL

Такой вариант безопаснее для отчётов.

Группы всегда возвращаются как text

Даже если вы захватили цифры, результат группы будет текстом.

Например:

SELECT (REGEXP_MATCH('order-4815', '^order-([0-9]+)$'))[1] AS order_no;

Результат выглядит как число:

4815

Но тип значения — text.

Если нужно сравнивать его как число, сортировать как число или записывать в integer-колонку, приведите тип явно:

SELECT ((REGEXP_MATCH('order-4815', '^order-([0-9]+)$'))[1])::int AS order_no;

Теперь order_no будет числом.

Это важно, потому что текстовая сортировка и числовая сортировка отличаются.

Текстовая сортировка:

1
10
2

Числовая сортировка:

1
2
10

Если вы достали число регуляркой, но не привели его к int, можно получить неправильный порядок.

Если в шаблоне нет групп

Если в регулярном выражении нет круглых скобок, REGEXP_MATCHES вернёт всё совпадение целиком.

Пример:

SELECT REGEXP_MATCHES('order-4815', '[0-9]+');

Результат:

{4815}

Здесь в шаблоне нет группы:

[0-9]+

Но совпадение всё равно найдено, поэтому в массив попала вся найденная подстрока.

Если группа есть:

SELECT REGEXP_MATCHES('order-4815', 'order-([0-9]+)');

результат будет таким же:

{4815}

Но смысл немного другой.

В первом случае массив содержит всё совпадение.

Во втором случае массив содержит то, что захватила первая группа.

Разница станет видна, если совпадение шире группы.

SELECT REGEXP_MATCHES('order-4815', '(order)-([0-9]+)');

Результат:

{order,4815}

Теперь две группы — два элемента массива.

Порядок групп

Группы нумеруются слева направо по открывающим скобкам.

Пример:

SELECT REGEXP_MATCHES('2026-06-19', '^([0-9]{4})-([0-9]{2})-([0-9]{2})$');

Результат:

{2026,06,19}

Группы такие:

[1] -> 2026
[2] -> 06
[3] -> 19

Можно достать их отдельно:

SELECT
    (REGEXP_MATCH('2026-06-19', '^([0-9]{4})-([0-9]{2})-([0-9]{2})$'))[1] AS year,
    (REGEXP_MATCH('2026-06-19', '^([0-9]{4})-([0-9]{2})-([0-9]{2})$'))[2] AS month,
    (REGEXP_MATCH('2026-06-19', '^([0-9]{4})-([0-9]{2})-([0-9]{2})$'))[3] AS day;

Результат:

year | month | day
-----+-------+----
2026 | 06    | 19

Но если вы работаете с настоящими датами, лучше использовать тип date и функции работы с датами. Регулярки нужны, когда дата пришла как текст и её нужно разобрать.

Флаг i: поиск без учёта регистра

Кроме g, у регулярных выражений есть и другие флаги.

Флаг i включает поиск без учёта регистра.

Пример без i:

SELECT REGEXP_MATCHES('Error: failed', 'error: (.+)');

Совпадения не будет, потому что в строке Error, а в шаблоне error.

С флагом i:

SELECT REGEXP_MATCHES('Error: failed', 'error: (.+)', 'i');

Результат:

{failed}

Теперь регистр не важен.

Флаги можно комбинировать:

'gi'

Это значит:

• g — искать все совпадения;
• i — не учитывать регистр.

Например:

SELECT REGEXP_MATCHES('SQL sql Sql', '(sql)', 'gi');

Результат:

{SQL}
{sql}
{Sql}

Пример: разобрать строку лога

Допустим, лог хранится в таком виде:

ERROR 2026-06-19 Payment failed

Хотим достать:

• уровень: ERROR;
• дату: 2026-06-19;
• сообщение: Payment failed.

Запрос:

SELECT REGEXP_MATCHES(
    'ERROR 2026-06-19 Payment failed',
    '^([A-Z]+)\s+([0-9]{4}-[0-9]{2}-[0-9]{2})\s+(.+)$'
);

Результат:

{ERROR,2026-06-19,"Payment failed"}

Чтобы вывести красиво по колонкам:

WITH parsed AS (
    SELECT REGEXP_MATCH(
        'ERROR 2026-06-19 Payment failed',
        '^([A-Z]+)\s+([0-9]{4}-[0-9]{2}-[0-9]{2})\s+(.+)$'
    ) AS m
)
SELECT
    m[1] AS level,
    m[2] AS event_date,
    m[3] AS message
FROM parsed;

Результат:

level | event_date | message
------+------------+----------------
ERROR | 2026-06-19 | Payment failed

Здесь я использовал REGEXP_MATCH, потому что нам нужно только первое совпадение и не нужно размножать строки.

Пример: вытащить все хэштеги

Вот задача, где REGEXP_MATCHES с флагом g действительно хорош.

Есть текст:

Learning #sql with #postgres and #regex

Нужно получить все хэштеги отдельными строками.

SELECT (REGEXP_MATCHES(
    'Learning #sql with #postgres and #regex',
    '#([a-z]+)',
    'g'
))[1] AS hashtag;

Результат:

hashtag
--------
sql
postgres
regex

Каждый хэштег стал отдельной строкой.

Если это делать для таблицы постов:

SELECT
    p.id,
    m.match[1] AS hashtag
FROM posts p
CROSS JOIN LATERAL REGEXP_MATCHES(p.body, '#([a-z]+)', 'g') AS m(match);

Результат может быть таким:

id | hashtag
---+----------
1  | sql
1  | postgres
1  | regex
2  | mysql
3  | clickhouse
3  | sql

Это удобный способ превратить текст с несколькими найденными фрагментами в нормальные строки результата.

Как собрать найденные значения обратно в массив

Иногда нужно не размножить строки, а получить массив найденных значений для каждой записи.

Например, для каждого поста собрать все хэштеги в массив.

SELECT
    p.id,
    array_agg(m.match[1] ORDER BY m.match[1]) AS hashtags
FROM posts p
CROSS JOIN LATERAL REGEXP_MATCHES(p.body, '#([a-z]+)', 'g') AS m(match)
GROUP BY p.id;

Результат:

id | hashtags
---+----------------------
1  | {postgres,regex,sql}
2  | {mysql}
3  | {clickhouse,sql}

Но тут есть нюанс: CROSS JOIN LATERAL не сохранит посты без хэштегов.

Если нужно сохранить все посты, используйте LEFT JOIN LATERAL:

SELECT
    p.id,
    array_remove(array_agg(m.match[1]), NULL) AS hashtags
FROM posts p
LEFT JOIN LATERAL REGEXP_MATCHES(p.body, '#([a-z]+)', 'g') AS m(match)
    ON true
GROUP BY p.id;

Так пост без хэштегов тоже останется в результате, просто массив будет пустым или без значений после удаления NULL.

REGEXP_MATCHES против REGEXP_MATCH

Выбирайте так:

REGEXP_MATCH — когда нужно первое совпадение и вы хотите получить массив или NULL.

SELECT (REGEXP_MATCH(name, '([0-9]+)'))[1] AS digits
FROM users;

Подходит для обычных отчётов, где строки без совпадения должны остаться.

REGEXP_MATCHES — когда нужно работать с набором строк, особенно с флагом g.

SELECT (REGEXP_MATCHES(tags, '([a-z]+)', 'g'))[1] AS tag
FROM articles;

Подходит, когда одна строка может дать несколько результатов.

Простое правило:

Нужно одно совпадение -> REGEXP_MATCH или REGEXP_SUBSTR
Нужны все совпадения  -> REGEXP_MATCHES с флагом g

REGEXP_MATCHES против REGEXP_SUBSTR

REGEXP_SUBSTR удобен, когда нужна одна подстрока, а не массив.

Например:

SELECT
    id,
    REGEXP_SUBSTR(name, '[0-9]+') AS digits
FROM users;

Если совпадения нет, будет NULL.

Это читается проще, чем:

SELECT
    id,
    (REGEXP_MATCH(name, '([0-9]+)'))[1] AS digits
FROM users;

Но REGEXP_SUBSTR больше подходит для одной подстроки.

Если нужно достать сразу несколько групп, например дату, уровень лога и сообщение, массив от REGEXP_MATCH или REGEXP_MATCHES может быть удобнее.

REGEXP_MATCHES и WHERE

Иногда регулярное выражение используют сначала как фильтр, а потом как извлечение.

Например:

SELECT
    id,
    code,
    (REGEXP_MATCHES(code, '^order-([0-9]+)$'))[1] AS order_no
FROM orders
WHERE code ~ '^order-[0-9]+$';

Здесь WHERE заранее оставляет только строки, которые подходят под шаблон.

Поэтому REGEXP_MATCHES в SELECT уже не выбросит неожиданно строки без совпадения: они были отфильтрованы явно.

Это лучше, чем полагаться на скрытое исчезновение строк из-за самой функции.

Но всё равно для одного совпадения часто проще использовать REGEXP_MATCH:

SELECT
    id,
    code,
    (REGEXP_MATCH(code, '^order-([0-9]+)$'))[1] AS order_no
FROM orders
WHERE code ~ '^order-[0-9]+$';

Так намерение запроса выглядит понятнее.

Производительность

Регулярные выражения мощные, но они не бесплатные.

Запрос вида:

SELECT *
FROM orders
WHERE code ~ '^order-[0-9]+$';

может быть тяжелее, чем обычное сравнение или поиск по индексу.

А если вы ещё извлекаете группы:

(REGEXP_MATCH(code, '^order-([0-9]+)$'))[1]

база должна применить регулярное выражение к строкам.

На маленьких таблицах это нормально. На больших таблицах лучше подумать о структуре данных.

Например, если order_no нужен часто, возможно, его стоит хранить отдельной колонкой:

code        | order_no
------------+---------
order-4815  | 4815
order-9021  | 9021

Тогда искать и сортировать можно по обычному числовому полю, а не доставать номер регуляркой каждый раз.

Регулярки хороши для импорта, чистки, разовых проверок и разбора грязного текста. Но если извлечённое значение стало важной частью модели данных, лучше хранить его явно.

MySQL и ClickHouse: есть ли REGEXP_MATCHES

REGEXP_MATCHES — это функция PostgreSQL. В MySQL такой функции с такой же семантикой нет.

В MySQL 8+ есть регулярные функции вроде:

REGEXP_LIKE
REGEXP_SUBSTR
REGEXP_REPLACE

Для одной найденной подстроки ближе всего REGEXP_SUBSTR.

Но поведения «вернуть несколько строк по всем совпадениям», как у PostgreSQL REGEXP_MATCHES(..., 'g'), в MySQL напрямую нет. Обычно это решают через дополнительные конструкции, JSON, рекурсивные CTE или обработку на уровне приложения.

В ClickHouse есть другие функции для похожих задач:

extract(...)
extractAll(...)
extractGroups(...)
extractAllGroupsHorizontal(...)
extractAllGroupsVertical(...)

Например, чтобы достать все числа из строки:

SELECT extractAll('order 42, user 105', '[0-9]+') AS numbers;

Результат:

['42','105']

То есть идея похожая — извлечь совпадения по регулярному выражению. Но форма результата другая: ClickHouse чаще возвращает массивы, а не размножает строки так, как это делает REGEXP_MATCHES в PostgreSQL.

Что тестировать

Перед тем как использовать регулярное извлечение в отчёте или миграции, проверьте несколько типов строк:

order-4815
draft
order-
order-abc
order-123-extra
NULL
''

И отдельно посмотрите:

• что происходит, когда совпадение есть;
• что происходит, когда совпадения нет;
• возвращается ли NULL или строка исчезает;
• сколько строк создаёт флаг g;
• какой тип у извлечённого значения;
• нужно ли приводить результат к int;
• не захватывает ли шаблон лишний текст.

Это особенно важно для REGEXP_MATCHES, потому что она может менять количество строк в результате.

Коротко

REGEXP_MATCHES в PostgreSQL ищет совпадения по регулярному выражению и возвращает результат как массив text[].

Пример:

SELECT REGEXP_MATCHES('alex@gmail.com', '^([^@]+)@(.+)$');

Результат:

{alex,gmail.com}

Главные правила:

• каждая группа в круглых скобках становится элементом массива;
• первая группа доступна как [1], вторая как [2];
• если групп нет, в массив попадает всё совпадение целиком;
• результат группы всегда имеет тип text;
• для чисел используйте явное приведение, например ::int;
• без флага g возвращается только первое совпадение;
• с флагом g возвращается отдельная строка на каждое совпадение;
• если совпадения нет, REGEXP_MATCHES возвращает ноль строк;
• из-за этого строки таблицы могут исчезнуть из результата;
• если нужно сохранить все строки, используйте REGEXP_MATCH, REGEXP_SUBSTR или LEFT JOIN LATERAL;
• если нужно получить все совпадения отдельными строками, используйте REGEXP_MATCHES с флагом g.

Самая важная мысль: REGEXP_MATCHES — не обычная функция, которая просто возвращает значение. Она может вернуть ноль, одну или много строк. Если это понимать, она становится мощным инструментом для разбора email, кодов заказов, логов, тегов и любых строк, где внутри спрятано несколько смысловых частей.