Shape / rows · All operations

Split delimited text into rows

A variant of Split a list into rows

Split a text value on a separator or pattern and emit one row per item.

Status

In arcform

Runs as a SQL step

arcform carries it as a sql: step today.

The DuckDB step

Split delimited text on a separator or a pattern, one row per item

It reads the orders table the proofs share. Create it first:

SQL

CREATE TABLE orders AS SELECT * FROM (VALUES
  (1,  'Alice Ng',      'north', DATE '2026-01-03', 120.00, 'widget', 'a|b',   '{"channel":"web","items":[1,2]}'),
  (2,  'alice ng',      'north', DATE '2026-01-05',  80.50, 'gadget', 'b',     '{"channel":"shop","items":[3]}'),
  (3,  'Bob  Okafor',   'south', DATE '2026-01-05', 300.00, 'widget', 'a|c',   '{"channel":"web","items":[4,5,6]}'),
  (4,  'Carla Díaz',    'east',  DATE '2026-01-09',  NULL,  'gizmo',  NULL,    '{"channel":"web","items":[]}'),
  (5,  'Bob Okafor',    'south', DATE '2026-01-12',  45.25, 'gadget', 'c',     '{"channel":"phone","items":[7]}'),
  (6,  'Dev Patel',     'west',  DATE '2026-01-12', 510.00, 'widget', 'a',     '{"channel":"web","items":[8,9]}'),
  (7,  'Carla Diaz',    'east',  DATE '2026-01-20',  99.99, 'gizmo',  'b|c',   '{"channel":"shop","items":[10]}'),
  (8,  'Dev Patel',     'west',  DATE '2026-02-02', 510.00, 'widget', 'a',     '{"channel":"web","items":[8,9]}'),
  (9,  'Eve Larsen',    NULL,    DATE '2026-02-03',  15.00, 'gadget', NULL,    '{"channel":"web","items":[11]}'),
  (10, 'Alice Ng',      'north', DATE '2026-02-10', 220.00, 'gizmo',  'a|b|c', '{"channel":"phone","items":[12,13]}')
) t(order_id, customer, region, order_date, amount, product, tags, meta);

Then run the statement:

SQL

-- orders.tags is separated by '|'; an order with no tags has no items, so it emits no row
CREATE TEMP TABLE by_separator AS
SELECT order_id, unnest(string_split(tags, '|')) AS tag FROM orders;
-- a pattern: commas, semicolons or bars, each with any spaces around it
CREATE TEMP TABLE notes AS SELECT * FROM (VALUES (1, 'red, green;blue'), (2, 'one | two')) t(id, colours);
CREATE TEMP TABLE by_pattern AS
SELECT id, unnest(regexp_split_to_array(colours, '\s*[,;|]\s*')) AS colour FROM notes;
SELECT * FROM by_separator ORDER BY order_id, tag;
SELECT * FROM by_pattern ORDER BY id, colour;
SELECT CASE WHEN ((SELECT count(*) FROM by_separator) = 13 AND (SELECT list(tag ORDER BY tag) FROM by_separator WHERE order_id = 10) = ['a', 'b', 'c'] AND (SELECT count(DISTINCT order_id) FROM by_separator) = 8 AND (SELECT list(colour ORDER BY colour) FROM by_pattern WHERE id = 1) = ['blue', 'green', 'red'] AND (SELECT list(colour ORDER BY colour) FROM by_pattern WHERE id = 2) = ['one', 'two']) THEN 'as expected' ELSE error('the tags did not split into 13 rows over 8 orders, or the colours did not split into three and two trimmed items') END AS verdict;

It needs nothing installed beyond DuckDB itself.
The statement checks its own result and raises an error when it is wrong: “the tags did not split into 13 rows over 8 orders, or the colours did not split into three and two trimmed items”.
It passed on DuckDB v2.0.0 (Cyanoptera) d54cbbe3df.

What other tools call it

Meridian's classification; an independent blind rating agreed on 95.7% of 324 rows (variant, version 3).

Apache Hop (1)

Split fields to rowsread 2026-09-27
Split a delimited field into one new row per value

Duckle (1)

Normalizeread 2026-09-25
explode a delimited or array column into rows

EasyMorph (1)

Split delimited text into rowsread 2026-09-27
Split a delimited text column into one row per item

OpenRefine (1)

Split multi-valued cells…read 2026-09-27
Split a cell into several rows by separator, regular expression or field lengths

Pentaho Data Integration (1)

Split field to rowsread 2026-09-27
Split a delimited string field into one row per term

Talend (1)

tNormalizeread 2026-09-27
Split a column of delimited items into one row per item

Trifacta (2)

Split into rowsread 2026-09-27
Split raw data into rows on a delimiter
Splitrows Transformread 2026-09-27
Split a column of text into separate rows on a literal delimiter

Background

In PostgreSQL, string_to_table splits a string at occurrences of a delimiter and returns the resulting fields as a set of text rows.^[1] In tidyr, separate_longer_delim splits a string into rows by a delimiter, and in Apache Hop, Split fields to rows makes one new row per split value.^[2]^[3] In OpenRefine, splitting multi-valued cells is a common way to get data from single rows into multi-row records.^[4] In PostgreSQL, regexp_split_to_table splits a string using a POSIX regular expression as the delimiter, producing a set of results.^[1]

When to use it

In SQL Server, STRING_SPLIT returns empty substrings where the input contains two or more consecutive delimiters, and an empty table for a NULL input.^[5] In OpenRefine, splitting on a separator removes the separator characters, so a space next to the separator may need to be included in it.^[4] Trim each piece and drop the empty ones after splitting, unless an empty piece means something in the source. (Meridian's view)

In Apache Hop, Split fields to rows takes an optional enclosure, and delimiters inside a pair of enclosure characters are not treated as split points.^[3] A delimiter that can also appear inside a value, such as a comma in a quoted name, needs an enclosure or a pattern rather than a plain split. (Meridian's view)

In SQL Server, the output rows of STRING_SPLIT might be in any order, and the order is not guaranteed to match the order of the substrings in the input.^[5] In SQL Server, passing 1 as the enable_ordinal argument of STRING_SPLIT adds an ordinal column holding each substring's 1-based position in the input.^[5] Keep each piece's position when the order of the list carries meaning, such as a primary category written first. (Meridian's view)

The method

In DuckDB, string_split splits a string along a separator into a list, and string_split_regex splits it along a regular expression.^[6]

In PostgreSQL, string_to_table with a NULL delimiter makes each character of the string a separate row, and with an empty delimiter treats the string as a single field.^[1] In PostgreSQL, string_to_table replaces fields matching its optional null_string argument with NULL.^[1] In tidyr, the delim of separate_longer_delim is interpreted as a fixed string by default, and stringr::regex() can be used to split in other ways.^[2]

Compared with the alternatives

In OpenRefine, joining multi-valued cells reverses the split, compressing the strings of a record into its topmost cell in the order they appear.^[4] In OpenRefine, data that should be split into multiple columns instead of multiple rows has a separate operation, Split into several columns.^[4] In tidyr, separate_longer_delim returns a data frame with the same columns as its input but different rows.^[2] Split to rows when the pieces are values of one kind with no fixed count, and to columns when each position has a meaning of its own, such as a first name and a surname. (Meridian's view)

Sources

[1]PostgreSQL: Documentation: 18: 9.4. String Functions and Operatorsdocumentation
[2]Split a string into rows — separate_longer_delim • tidyrdocumentation
[3]Split fields to rows | Apache Hopdocumentation
[4]Cell editing | OpenRefinedocumentation
[5]STRING_SPLIT (Transact-SQL) - SQL Server | Microsoft Learndocumentation
[6]Text Functions – DuckDBdocumentation