REGEXP_SPLIT_TO_ARRAY: Splitting Strings by a Regex Delimiter

When data arrives crammed into one text column — comma-separated tags, an email list with stray spaces, a manager-path string — a fixed delimiter stops being enough. REGEXP_SPLIT_TO_ARRAY and REGEXP_SPLIT_TO_TABLE cut a string on a regular expression, swallowing irregular whitespace and repeated separators in a single pass.

Basic regex splitting

Both functions take a string and a regex delimiter. The first returns a text[] array; the second returns a set of rows.

-- 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;

The delimiter \s*,\s* means "a comma surrounded by any amount of whitespace." So b,c and , d come out equally clean, with no per-element TRIM to babysit.

CSV-ish input and UNNEST

Say users.name temporarily holds several names separated by commas, or you received a country list as one string. Split into an array, then expand it with 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 gives the same shape without the intermediate array:

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

The class [;,] splits on either a comma or a semicolon — the usual reality when export formats are mixed.

When SPLIT_PART is enough

If the delimiter is exactly one character and you want a specific segment by index, SPLIT_PART is simpler and faster: it never spins up the regex engine.

-- 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;

Choosing rule of thumb:

• Fixed single delimiter + you want the Nth piece → SPLIT_PART.
• Variable whitespace, multiple delimiter variants, you want every part → REGEXP_SPLIT_TO_ARRAY / _TABLE.

Gotcha: empty elements and anchors

SPLIT_PART is 1-indexed and returns an empty string (not NULL) on a miss. Regex splitting has its own trap: if the delimiter matches at the start or end of the string, you get empty elements.

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

Trim the input first, or filter after UNNEST:

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

Differences in other engines

• MySQL has no direct equivalent: before 8.0 you expand a string with a recursive CTE over SUBSTRING_INDEX; 8.0.4+ adds REGEXP_SUBSTR/REGEXP_REPLACE, but no split-to-table — JSON_TABLE is often easier.
• ClickHouse uses splitByRegexp(pattern, s) (and splitByChar for a fixed character), returning Array(String), which you expand with arrayJoin.
• In PostgreSQL, flags go in a third argument: regexp_split_to_array(s, 'x', 'i') for a case-insensitive match.

Keep SPLIT_PART for clean single-character cases, and reach for REGEXP_SPLIT_TO_ARRAY for anything even slightly messy.