Shape / columns · All operations

Split text into a list

A variant of Split a column

Split a text value into one list value of its parts, in a single column.

Status

In arcform

Runs as a SQL step

arcform carries it as a sql: step today.

The DuckDB step

Split text into one list column

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
CREATE TEMP TABLE result AS
SELECT order_id, string_split(tags, '|') AS tag_list FROM orders;
SELECT * FROM result;
SELECT CASE WHEN ((SELECT list(column_name) FROM (DESCRIBE result)) = ['order_id', 'tag_list'] AND (SELECT tag_list FROM result WHERE order_id = 10) = ['a', 'b', 'c'] AND (SELECT sum(len(tag_list)) FROM result) = 13 AND (SELECT count(*) FILTER (WHERE tag_list IS NULL) FROM result) = 2) THEN 'as expected' ELSE error('the tags were not split into one list column of 13 parts with the two missing tags left null') 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 were not split into one list column of 13 parts with the two missing tags left null”.
  • 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).

AWS Glue (1)
  • Split Stringread 2026-09-27

    Break a string into an array of tokens at a regular expression

Duckle (1)
  • Splitread 2026-09-25

    split a column into a list

Ibis (1)
  • StringSplitread 2026-09-27

    Split a string into an array at a delimiter

OpenRefine (1)
Polars (1)
  • Expr.str: split (group)read 2026-09-27

    4 expressions: split, split_exact, splitn, explode; break a string into a list, a struct of parts, or characters

Power Query M (1)
  • Lines functionsread 2026-09-27

    4 functions: Lines.FromBinary, Lines.FromText, Lines.ToBinary, Lines.ToText

Background

A split function divides a string at each occurrence of a separator and returns a list, or array, of the substrings.[1] In DuckDB, string_split splits a string along a separator, so hello-world becomes the list [hello, world].[2] In DuckDB, every value in a LIST column must share one data type, but each row can have any number of elements.[3]

When to use it

Survey data frequently allows respondents to select all that apply, and an inventory list might file items under more than one category.[4] In DuckDB, list_contains returns true if a list contains an element, and list_has_any returns true if two lists have any element in common.[5] Split into a list when the number of parts varies from row to row and none of them has a column's worth of meaning on its own, as with tags. (Meridian's view)

In PostgreSQL, arrays are not sets, and searching for specific array elements can be a sign of database misdesign.[6] For PostgreSQL, a separate table with a row for each item that would be an array element is easier to search and likely to scale better for a large number of elements.[6] A table is in first normal form if each field is atomic, containing a single value rather than a set of values or a nested table.[7] Treat the list as a step on the way, to count, test or unnest the parts, rather than the shape a table is kept in. (Meridian's view)

In PostgreSQL, string_to_array treats the string as a single field when the delimiter is an empty string, and makes each character an element when the delimiter is NULL.[8] PostgreSQL's string_to_array takes an optional null_string, and fields matching it become NULL in the array.[8] In OpenRefine, split preserves empty segments when its preserveTokens option is set to true.[9] Decide whether two separators in a row mean an empty part or a typing slip, and check that the split you use agrees. (Meridian's view)

The method

Some split functions take a limit, and after limit minus 1 separators the rest of the string becomes the last substring, whether or not it holds more separators.[1] In DuckDB, string_split_regex splits along a regular expression, so a pattern of an optional semicolon and a space splits hello world; 42 into [hello, world, 42].[2] PostgreSQL's regexp_split_to_array splits a string using a POSIX regular expression as the delimiter and produces an array of the results.[8]

DuckDB's LIST type is similar to PostgreSQL's ARRAY type, and DuckDB uses its own ARRAY type for fixed-length lists.[10] In DuckDB, a list element is extracted with a 1-based index, and ['a', 'b', 'c'][-1] returns 'c'.[10][5] In DuckDB, length returns the number of elements in a list.[5]

Compared with the alternatives

In DuckDB, empty and NULL lists both unnest to zero rows.[11]

DuckDB's regexp_split_to_table splits a string along a regular expression and returns a row for each part.[2] PostgreSQL's string_to_table splits a string at a delimiter and returns the fields as a set of text rows.[8]

Sources

  1. [1]Comparison of programming languages (string functions) - Wikipediasecondary source
  2. [2]Text Functions – DuckDBdocumentation
  3. [3]Data Types – DuckDBdocumentation
  4. [4]Cell editing | OpenRefinedocumentation
  5. [5]List Functions – DuckDBdocumentation
  6. [6]PostgreSQL: Documentation: 18: 8.15. Arraysdocumentation
  7. [7]First normal form - Wikipediasecondary source
  8. [8]PostgreSQL: Documentation: 18: 9.4. String Functions and Operatorsdocumentation
  9. [9]GREL functions | OpenRefinedocumentation
  10. [10]List Type – DuckDBdocumentation
  11. [11]Unnesting – DuckDBdocumentation