REGEXP_SPLIT_TO_ARRAY и REGEXP_SPLIT_TO_TABLE в PostgreSQL: как разбивать грязные строки по шаблону

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

В реальном мире всё иначе.

В одной строке может лежать список тегов:

sql, postgres, joins

В другой — те же теги, но с лишними пробелами:

sql,postgres ,  joins

А в третьей кто-то вместо запятой поставил точку с запятой:

sql; postgres, joins

Если разделитель всегда один и тот же, можно обойтись простыми функциями вроде split_part. Но когда формат «плавает», нужен инструмент гибче. В PostgreSQL для этого есть две функции:

• regexp_split_to_array;
• regexp_split_to_table.

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

Зачем нужны regex-разбиения

Представим, что в таблицу импортировали данные из внешней системы. В поле tags лежит список тегов.

sql, postgres,joins ,  index

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

Можно было бы сначала разбить строку по запятой, потом каждый элемент отдельно прогнать через trim, потом где-то ещё почистить двойные пробелы. Но это быстро превращается в набор заплаток.

С регулярным выражением можно описать разделитель точнее:

SELECT regexp_split_to_array('sql, postgres,joins ,  index', '\s*,\s*') AS tags;

Результат:

{sql,postgres,joins,index}

Шаблон \s*,\s* означает:

• \s* — любое количество пробельных символов;
• , — запятая;
• \s* — снова любое количество пробельных символов.

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

Две функции: массив или строки

У PostgreSQL есть две похожие функции.

regexp_split_to_array возвращает массив:

SELECT regexp_split_to_array('a, b,c ,  d', '\s*,\s*') AS items;

Результат:

{a,b,c,d}

А regexp_split_to_table возвращает набор строк:

SELECT regexp_split_to_table('a, b,c ,  d', '\s*,\s*') AS item;

Результат:

item
----
a
b
c
d

Разница важная.

regexp_split_to_array удобно использовать, когда вам нужен результат как одно значение типа text[]. Например, сохранить массив в колонку, передать дальше в функцию или потом отдельно развернуть через unnest.

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

Проще говоря:

• нужен массив — берите regexp_split_to_array;
• нужны отдельные строки — берите regexp_split_to_table.

Базовый пример: список тегов

Допустим, у нас есть строка с тегами. Разделитель — запятая, но пробелы вокруг неё могут быть любыми.

SELECT regexp_split_to_array('sql, postgres,joins ,  indexes', '\s*,\s*') AS tags;

Результат:

{sql,postgres,joins,indexes}

Теперь тот же пример, но сразу в строки:

SELECT regexp_split_to_table('sql, postgres,joins ,  indexes', '\s*,\s*') AS tag;

Результат:

tag
--------
sql
postgres
joins
indexes

Для новичка здесь главное понять: вторым аргументом идёт не обычный текстовый разделитель, а regex-шаблон.

Если вы пишете просто ',', это почти то же самое, что разбиение по обычной запятой. Но если пишете '\s*,\s*', вы уже говорите: «разделитель — это запятая, вокруг которой могут быть пробелы».

Разбиение по нескольким разделителям

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

ann@example.com; bob@example.com, kate@example.com

Можно разбить такую строку по запятой или точке с запятой за один проход:

SELECT regexp_split_to_table(
    'ann@example.com; bob@example.com, kate@example.com',
    '\s*[;,]\s*'
) AS email;

Результат:

email
----------------
ann@example.com
bob@example.com
kate@example.com

Шаблон \s*[;,]\s* читается так:

• сначала могут быть пробелы;
• потом один символ из набора [;,];
• потом снова могут быть пробелы.

Квадратные скобки в регулярном выражении означают «любой один символ из перечисленных». Поэтому [;,] ловит и запятую, и точку с запятой.

Это как раз тот случай, где обычный split_part уже неудобен: разделитель не один, а несколько возможных.

Развернуть массив через unnest

Иногда удобнее сначала получить массив, а потом развернуть его в строки.

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

WITH raw(id, countries) AS (
    VALUES
        (1, 'US,  CA ,MX'),
        (2, 'BR , AR')
)
SELECT
    r.id,
    c.country
FROM raw r
CROSS JOIN LATERAL unnest(
    regexp_split_to_array(r.countries, '\s*,\s*')
) AS c(country);

Результат:

id | country
---+--------
1  | US
1  | CA
1  | MX
2  | BR
2  | AR

Здесь происходит три шага.

Сначала regexp_split_to_array превращает строку в массив:

US,  CA ,MX

становится:

{US,CA,MX}

Потом unnest разворачивает массив в строки.

А CROSS JOIN LATERAL позволяет сделать это для каждой строки исходной таблицы.

На человеческом языке запрос говорит:

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

То же самое через regexp_split_to_table

Можно обойтись без промежуточного массива.

WITH raw(id, countries) AS (
    VALUES
        (1, 'US,  CA ,MX'),
        (2, 'BR , AR')
)
SELECT
    r.id,
    c.country
FROM raw r
CROSS JOIN LATERAL regexp_split_to_table(
    r.countries,
    '\s*,\s*'
) AS c(country);

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

id | country
---+--------
1  | US
1  | CA
1  | MX
2  | BR
2  | AR

regexp_split_to_table сразу возвращает набор строк, поэтому отдельный unnest не нужен.

Когда нужен номер элемента

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

Например, строка:

US, CA, MX

Здесь US идёт первым, CA вторым, MX третьим.

Если порядок важен, используйте WITH ORDINALITY.

WITH raw(id, countries) AS (
    VALUES
        (1, 'US, CA, MX')
)
SELECT
    r.id,
    c.country,
    c.position
FROM raw r
CROSS JOIN LATERAL unnest(
    regexp_split_to_array(r.countries, '\s*,\s*')
) WITH ORDINALITY AS c(country, position);

Результат:

id | country | position
---+---------+---------
1  | US      | 1
1  | CA      | 2
1  | MX      | 3

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

Например, в строке ceo/vp/manager/lead каждый следующий элемент находится ниже в иерархии.

WITH raw(employee_id, manager_path) AS (
    VALUES
        (101, 'ceo / vp / manager / lead')
)
SELECT
    r.employee_id,
    p.manager_role,
    p.level_number
FROM raw r
CROSS JOIN LATERAL unnest(
    regexp_split_to_array(r.manager_path, '\s*/\s*')
) WITH ORDINALITY AS p(manager_role, level_number);

Результат:

employee_id | manager_role | level_number
------------+--------------+-------------
101         | ceo          | 1
101         | vp           | 2
101         | manager      | 3
101         | lead         | 4

Шаблон \s*/\s* означает: слэш с возможными пробелами вокруг.

Когда достаточно split_part

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

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

Например, получить домен из чистого email:

SELECT
    id,
    split_part(email, '@', 2) AS domain
FROM users;

Если email равен ann@example.com, результатом будет:

example.com

Или взять верхний отдел из пути:

SELECT
    id,
    split_part(dept_path, '/', 1) AS top_dept
FROM employees;

Если dept_path равен eng/backend/payments, результатом будет:

eng

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

Хорошее правило:

• один понятный разделитель и нужен один конкретный кусок — используйте split_part;
• разделителей несколько, есть лишние пробелы или нужны все части — используйте regexp_split_to_array или regexp_split_to_table.

Пустые элементы: главная ловушка

У regex-разбиения есть важная особенность: если разделитель стоит в начале или в конце строки, в результате появятся пустые элементы.

SELECT regexp_split_to_array(',a,b,', ',') AS items;

Результат:

{"",a,b,""}

Почему так происходит?

Строка начинается с запятой. Значит, до первой запятой находится пустой кусок. Строка заканчивается запятой. Значит, после последней запятой тоже пустой кусок.

В реальных данных такое встречается часто:

sql,postgres,

или так:

,sql,postgres

или даже так:

sql,,postgres

Если пустые элементы не нужны, их надо отфильтровать.

WITH raw(id, tags) AS (
    VALUES
        (1, ',sql,postgres,'),
        (2, 'joins,,indexes')
)
SELECT
    r.id,
    t.tag
FROM raw r
CROSS JOIN LATERAL regexp_split_to_table(
    r.tags,
    '\s*,\s*'
) AS t(tag)
WHERE t.tag <> '';

Результат:

id | tag
---+---------
1  | sql
1  | postgres
2  | joins
2  | indexes

Условие t.tag <> '' убирает пустые строки.

NULL и пустая строка

Важно различать NULL и пустую строку.

Если на вход пришёл NULL, результат тоже будет NULL.

SELECT regexp_split_to_array(NULL, '\s*,\s*') AS items;

Результат:

NULL

Если на вход пришла пустая строка, это уже не NULL. Это строка длиной ноль. Она превращается в массив с одним пустым элементом.

SELECT regexp_split_to_array('', '\s*,\s*') AS items;

Результат:

{""}

Для импорта данных это важное отличие.

NULL обычно означает «значения нет». Пустая строка означает «значение есть, но оно пустое». В отчётах и очистке данных эти случаи часто нужно обрабатывать по-разному.

Например, можно убрать пустые элементы после разбиения:

WITH raw(id, tags) AS (
    VALUES
        (1, ''),
        (2, NULL),
        (3, 'sql, postgres')
)
SELECT
    r.id,
    t.tag
FROM raw r
CROSS JOIN LATERAL regexp_split_to_table(
    r.tags,
    '\s*,\s*'
) AS t(tag)
WHERE t.tag <> '';

Строка с NULL не даст элементов, а пустой элемент из пустой строки будет отфильтрован.

Осторожно с шаблонами, которые совпадают с пустотой

Разделитель должен находить реальные границы между частями строки.

Плохая идея — использовать шаблон, который может совпасть с пустым местом. Например, пустой шаблон или слишком широкий шаблон вроде .*.

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

Например, не стоит писать так:

SELECT regexp_split_to_array('abc', '');

Для рабочих запросов выбирайте явный разделитель:

SELECT regexp_split_to_array('a, b, c', '\s*,\s*') AS items;

или:

SELECT regexp_split_to_array('a; b, c', '\s*[;,]\s*') AS items;

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

Метасимволы нужно экранировать

В регулярных выражениях некоторые символы имеют специальный смысл.

Например:

• . означает почти любой символ;
• + означает «один или больше повторов»;
• | означает «или»;
• [ и ] задают набор символов;
• ( и ) задают группу.

Если вы хотите разбивать строку именно по точке, точку нужно экранировать.

SELECT regexp_split_to_array('app.api.users', '\.') AS parts;

Результат:

{app,api,users}

Если написать просто '.', получится совсем другой смысл: точка в regex означает «любой символ».

То же самое касается других специальных символов. Если разбиение внезапно работает странно, первым делом проверьте: не забыли ли вы экранировать метасимвол.

Флаги регулярных выражений

У функций regexp_split_to_array и regexp_split_to_table есть третий аргумент — флаги.

Например, флаг i включает регистронезависимое совпадение.

SELECT regexp_split_to_array('oneXtwoxtHree', 'x', 'i') AS parts;

Результат:

{one,two,tHree}

Без флага i шаблон x не совпал бы с большой буквой X.

Флаги нужны не каждый день, но полезно знать, что они есть. Особенно если вы разбираете данные, где регистр гуляет: то X, то x, то другие варианты.

Практический пример: очистить список email

Допустим, в таблице users есть колонка email_list, куда временно попали несколько email в одной строке. Разделители могут быть разные: запятая или точка с запятой.

WITH users(id, email_list) AS (
    VALUES
        (1, 'ann@example.com, bob@example.com'),
        (2, 'kate@example.com; max@example.com'),
        (3, 'tom@example.com,  sam@example.com; lee@example.com')
)
SELECT
    u.id,
    e.email
FROM users u
CROSS JOIN LATERAL regexp_split_to_table(
    u.email_list,
    '\s*[;,]\s*'
) AS e(email)
WHERE e.email <> '';

Результат:

id | email
---+-----------------
1  | ann@example.com
1  | bob@example.com
2  | kate@example.com
2  | max@example.com
3  | tom@example.com
3  | sam@example.com
3  | lee@example.com

Этот запрос делает сразу несколько полезных вещей:

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

Для импорта и временной очистки данных это очень удобный приём.

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

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

Если вы один раз очищаете импортную таблицу — всё нормально. Можно спокойно использовать regexp_split_to_table, проверить результат и переложить данные в нормальную структуру.

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

Например:

SELECT *
FROM users u
WHERE 'vip' = ANY(regexp_split_to_array(u.tags, '\s*,\s*'));

Такой запрос должен для каждой строки взять u.tags, разбить строку в массив и проверить наличие vip. Обычный индекс по колонке tags здесь обычно не спасает, потому что PostgreSQL работает не с исходным текстом, а с результатом функции.

Для маленьких таблиц это может быть незаметно. Для больших — лучше подумать о нормализации.

Вместо одной строки:

sql,postgres,indexes

лучше хранить теги отдельными строками в связующей таблице:

user_id | tag
--------+---------
1       | sql
1       | postgres
1       | indexes

Тогда по колонке tag можно построить обычный индекс и искать быстро.

Если данные всё же нужно хранить массивом, можно рассмотреть массивы и подходящие индексы под конкретные запросы. Главное — не превращать регулярное разбиение текстовой колонки в ежедневную обязанность для каждой строки большой таблицы.

Отличия от других СУБД

regexp_split_to_array и regexp_split_to_table — это функции PostgreSQL.

В других базах похожие задачи решаются иначе.

В MySQL нет полного прямого аналога, который так же просто разбивает строку регулярным выражением сразу в набор строк. В простых случаях используют строковые функции, рекурсивные CTE, REGEXP_SUBSTR, REGEXP_REPLACE или переводят список в JSON и разбирают через JSON_TABLE.

В ClickHouse есть функция splitByRegexp, которая возвращает массив строк.

SELECT splitByRegexp('\s*,\s*', 'a, b,c') AS items;

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

SELECT arrayJoin(splitByRegexp('\s*,\s*', 'a, b,c')) AS item;

Для фиксированного символа в ClickHouse есть более простая функция splitByChar.

При переносе запросов между СУБД важно не искать одинаковые названия функций, а переносить саму идею:

если разделитель простой — используем простое разбиение; если формат грязный — разбиваем по шаблону; если результат нужен для поиска — лучше нормализовать данные.

Как выбрать функцию

Выбор можно свести к нескольким понятным правилам.

Если нужно достать второй кусок email после @, берите split_part.

SELECT split_part(email, '@', 2) AS domain
FROM users;

Если нужно разбить строку на все элементы и получить массив, берите regexp_split_to_array.

SELECT regexp_split_to_array(tags, '\s*,\s*') AS tag_list
FROM articles;

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

SELECT
    a.id,
    t.tag
FROM articles a
CROSS JOIN LATERAL regexp_split_to_table(
    a.tags,
    '\s*,\s*'
) AS t(tag);

Если важен порядок элементов, используйте массив и WITH ORDINALITY.

SELECT
    t.tag,
    t.position
FROM unnest(
    regexp_split_to_array('sql, postgres, indexes', '\s*,\s*')
) WITH ORDINALITY AS t(tag, position);

Главное из статьи

regexp_split_to_array и regexp_split_to_table нужны, когда строку надо разбить не по одному фиксированному символу, а по шаблону.

regexp_split_to_array возвращает массив text[].

regexp_split_to_table возвращает набор строк.

Шаблон \s*,\s* удобен для списков через запятую с любыми пробелами вокруг разделителя.

Шаблон \s*[;,]\s* помогает разобрать список, где разделителем может быть запятая или точка с запятой.

Если нужен конкретный кусок по простому разделителю, чаще достаточно split_part.

Если после разбиения появляются пустые элементы, фильтруйте их условием item <> ''.

Если порядок элементов важен, используйте WITH ORDINALITY.

И главное: regex-разбиение отлично подходит для грязного импорта и разовой очистки, но для постоянного поиска по большим данным лучше хранить значения в нормальной структуре — отдельными строками, массивом или другой подходящей моделью, а не разбирать одну длинную строку на каждом запросе.