REGEXP_SPLIT_TO_ARRAY: Splitting Strings by a Regex Delimiter

Когда данные приходят в одном текстовом поле — теги через запятую, список email с лишними пробелами, путь иерархии менеджеров — фиксированный разделитель уже не спасает. REGEXP_SPLIT_TO_ARRAY и REGEXP_SPLIT_TO_TABLE режут строку по регулярному выражению, поглощая нерегулярные пробелы и повторяющиеся разделители за один проход.

Главное отличие от SPLIT_PART в том, что разделителем здесь служит не фиксированный символ, а шаблон. Один и тот же regexp_split_to_array(s, '\s*,\s*') поглощает запятую с любым числом пробелов вокруг, а класс [;,] ловит и запятую, и точку с запятой за один проход — ровно то, что нужно для импортных полей, где формат экспорта плавает. REGEXP_SPLIT_TO_ARRAY возвращает готовый text[], REGEXP_SPLIT_TO_TABLE — сразу набор строк, так что после разбиения остаётся только развернуть результат через UNNEST или присоединить к таблице. Из-за того что движок regex видит разделитель целиком, вы избегаете россыпи вложенных TRIM и REPLACE, которыми обычно латают разбор по одному символу.

Базовый разбор по regex

Обе функции принимают строку и regex-разделитель. Первая возвращает массив text[], вторая — набор строк.

-- Tolerate any whitespace around commas
SELECT regexp_split_to_array('a, b,c ,  d', '\s*,\s*');
-- {a,b,c,d}

-- Same delimiter, one row per element
SELECT regexp_split_to_table('a, b,c ,  d', '\s*,\s*') AS tag;

Разделитель \s*,\s* означает "запятая в окружении любого числа пробелов". Поэтому b,c и , d обрабатываются одинаково чисто, без ручного TRIM на каждом элементе. Обратите внимание: вторым аргументом идёт именно шаблон, а не литерал, поэтому метасимволы вроде точки или скобок придётся экранировать обратным слешем, если вы хотите разбивать строго по ним. Для разделителя из одного-двух обычных символов это незаметно, но как только в шаблоне появляются ., | или +, проверьте на паре строк, что разбиение идёт по тем границам, которые вы имели в виду.

CSV-подобный ввод и UNNEST

Допустим, в users поле name временно хранит несколько имён через запятую, или вы получили список стран одной строкой. Разбираем в массив, затем разворачиваем через 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);

REGEXP_SPLIT_TO_TABLE даёт тот же результат без промежуточного массива:

SELECT u.id,
       regexp_split_to_table(u.email, '[;,]\s*') AS one_email
FROM users u
WHERE u.email LIKE '%,%' OR u.email LIKE '%;%';

Класс [;,] разбивает и по запятой, и по точке с запятой — типичный случай, когда форматы экспорта смешаны. Разница между двумя подходами не только стилистическая: regexp_split_to_array даёт один массив на строку, и его удобно сохранить в колонку типа text[] или передать дальше как единое значение, а regexp_split_to_table сразу умножает строки набором, поэтому он естественно ложится в SELECT без явного unnest. Когда нужен и порядковый номер элемента, оборачивайте unnest(...) WITH ORDINALITY — массивная форма сохраняет позиции, а это важно, если из строки 'US, CA, MX' вам нужно знать, что MX шёл третьим.

Когда хватает SPLIT_PART

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

-- Domain part of a clean email
SELECT id, split_part(email, '@', 2) AS domain
FROM users;

-- Top-level dept from a path like 'eng/backend/payments'
SELECT id, split_part(dept, '/', 1) AS top_dept
FROM employees;

Правило выбора:

• Фиксированный одиночный разделитель + нужен N-й кусок → SPLIT_PART.
• Переменные пробелы, несколько вариантов разделителя, нужны все части → REGEXP_SPLIT_TO_ARRAY / _TABLE.

Подводный камень: пустые элементы и якоря

SPLIT_PART нумеруется с 1 и на промахе возвращает пустую строку, а не NULL. У regex-разбиения своя ловушка: если разделитель совпадает в начале или конце строки, вы получите пустые элементы.

-- Leading/trailing comma produces empty slots
SELECT regexp_split_to_array(',a,b,', ',');
-- {"",a,b,""}

Чистим заранее или фильтруем после UNNEST:

SELECT id, amount
FROM orders, LATERAL unnest(
  regexp_split_to_array(status, '\s*,\s*')
) AS s(item)
WHERE s.item <> '';

Различия в других СУБД

• MySQL не имеет прямого аналога: до 8.0 строку разворачивают через рекурсивный CTE с SUBSTRING_INDEX, в 8.0.4+ доступны REGEXP_SUBSTR/REGEXP_REPLACE, но разбиения в таблицу нет; часто проще JSON_TABLE.
• ClickHouse использует splitByRegexp(pattern, s) (и splitByChar для фиксированного символа), возвращая Array(String), который разворачивается через arrayJoin.
• В PostgreSQL флаги задаются третьим аргументом: regexp_split_to_array(s, 'x', 'i') для регистронезависимого совпадения.

Ещё одна тонкость REGEXP_SPLIT_TO_ARRAY — обращение с NULL и пустой строкой. На входе NULL функция возвращает NULL, а не пустой массив, поэтому после UNNEST такая строка просто не даст ни одной записи; пустая строка '' даёт массив из единственного пустого элемента {""}, и его, как и краевые "" из примера выше, отсекает условие s.item <> ''. Если же разделитель в шаблоне способен совпасть с пустой строкой (например, '' или '.*'), Postgres вернёт массив посимвольно или вовсе пустой — поведение, которое стоит проверить на реальных данных до того, как запрос попадёт в импорт.

Когда regexp_split_to_array стоит прямо в WHERE или JOIN над колонкой, обычный B-tree индекс по этой колонке не применяется: планировщик считает массив для каждой строки. Для разовой очистки это нормально, но для регулярного разбора по большой таблице разумнее разложить значения один раз и сохранить нормализованную форму или построить выражение/GIN индекс под конкретный шаблон, а не гонять regex на каждом запросе.

Держите SPLIT_PART для чистых одно-символьных случаев, а REGEXP_SPLIT_TO_ARRAY — для всего, что хоть немного грязное.