Shape / columns · All operations

Split a column

Split one column into several by delimiter, position or pattern.

Status

In arcform

Runs as a SQL step

arcform carries it as a sql: step today.

Shape of the result
one row out per row in, with columns added or changed
Level
one clause of SQL

The DuckDB step

Split one column into two

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 customer, split_part(customer, ' ', 1) AS first_name, split_part(customer, ' ', -1) AS last_name FROM orders;
SELECT * FROM result;
SELECT CASE WHEN ((SELECT count(*) FILTER (WHERE customer = 'Bob  Okafor' AND first_name = 'Bob' AND last_name = 'Okafor') = 1 FROM result)) THEN 'as expected' ELSE error('a name with two spaces did not split into its first and last parts') END AS verdict;
  • It needs nothing installed beyond DuckDB itself.
  • The statement checks its own result and raises an error when it is wrong: “a name with two spaces did not split into its first and last parts”.
  • It passed on DuckDB v2.0.0 (Cyanoptera) d54cbbe3df.

Variants

Split a column on a delimiter

Split a text column into several columns at a separator or pattern.

Spread a list into columns

Turn the elements of an array, vector or collection column into one column each.

What other tools call it

Meridian's classification; an independent blind rating agreed on 86.2% of 312 rows (exact class, version 2).

Alteryx Designer (1)
  • Text To Columnsread 2026-09-27

    Split a text column on delimiters into several columns or into rows

Apache Hop (2)
  • Split Fieldsread 2026-09-27

    Split one field into several by a delimiter

  • String cutread 2026-09-27

    Extract a substring by start and end position

Apache SeaTunnel (1)
  • Splitread 2026-09-27

    Split one field into several fields by a separator

AWS Glue (2)
  • Array To Columnsread 2026-09-27

    Extract some or all elements of an array column into new columns

  • Split Stringread 2026-09-27

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

Duckle (2)
  • Splitread 2026-09-25

    split a column into a list

  • Text to Columnsread 2026-09-25

    split a delimited column into named columns with split_part

EasyMorph (2)
Ibis (1)
  • StringSplitread 2026-09-27

    Split a string into an array at a delimiter

KNIME Analytics Platform (6)
OpenRefine (2)
Pentaho Data Integration (2)
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 (3)
  • Lines functionsread 2026-09-27

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

  • Splitter functionsread 2026-09-27

    10 functions returning text splitters: …SplitTextByDelimiter, …ByPositions, …ByLengths, …ByWhitespace, …ByCharacterTransition

  • Table.SplitColumnread 2026-09-27

    Split one column into several with a splitter function

RapidMiner Studio (1)
  • Splitread 2026-09-27

    Split nominal values by a pattern into several columns, ordered or as indicator columns

Tableau Prep (3)
  • Automatic Splitread 2026-09-27

    Split a field on a separator detected automatically

  • Custom Splitread 2026-09-27

    Split a field on a separator and occurrence that the user specifies

  • Split Valuesread 2026-09-27

    Split a field into several fields

Talend (2)
Trifacta (10)
VisiData (2)
  • addcol-splitread 2026-09-23

    Add column splitting current column by a regex

  • split-colread 2026-09-23

    (deprecated) Add columns from a regex split of current column

Background

In OpenRefine, one column can be split into several columns on a character, on string lengths or on an evaluating expression.[1] A delimiter is a character or a sequence of characters that marks the boundary between separate, independent regions in data.[2] In Tableau, a string field holding several distinct pieces of information, such as a customer's first and last name, may be able to be split into fields.[3]

When to use it

Delimiter collision happens when content contains a delimiter, so the embedded delimiter is wrongly read as a data boundary unless provisions are made to prevent it.[2] OpenRefine's smartSplit function splits on a separator while handling quotes properly and understanding cancelled characters.[4] Before splitting, check whether the separator can appear inside a value, such as a comma inside a quoted name. (Meridian's view)

In DuckDB, split_part returns an empty string when the requested part is outside the bounds of the list, to match PostgreSQL's behaviour.[5] Decide whether a missing part should read as empty or as missing, and test a row that has fewer parts than the rest. (Meridian's view)

In OpenRefine, splitting "fire, water, earth, air" on a comma keeps the space at the start of each later part, because only the separator is removed.[4] In OpenRefine, splitting on a separator removes the separator characters, so a space that follows it in the data can be included in the separator, as in a semicolon and a space.[6] Trim the parts after splitting on a single character, or split on a pattern that takes the surrounding spaces with it. (Meridian's view)

In OpenRefine, a maximum number of new columns can be set, and the separators after that limit are ignored, leaving the remaining characters in the last column.[1] In Tableau, fields cannot be split automatically when the separator types differ, and regular expressions are suggested for such fields.[3] Cast each part to its own type once it is split, since a year or a quantity cut from text is still text. (Meridian's view)

The method

A flat file stored as plain text is typically formatted as one record per line, either delimiter-separated or fixed-width.[7] Power Query's splitter functions split text by a delimiter, by lengths, by positions, by ranges, by whitespace or by a transition from one kind of character to another.[8]

In PostgreSQL, split_part splits a string at occurrences of a delimiter and returns the n'th field counting from one, or counts from the last field when n is negative.[9] In Tableau, the SPLIT function returns one result field per calculation, chosen by a token number, so splitting a customer name into first and last names takes two calculated fields.[3] Writing one expression per part keeps every new column named and typed on purpose, which is worth the extra lines. (Meridian's view)

Compared with the alternatives

OpenRefine's split multi-valued cells operation turns a cell holding several values into several rows, a common way to get data from single rows into multi-row records.[6] Joining columns is the reverse of splitting one in OpenRefine, as when several columns of category values are joined into one category column.[1] Split into columns when every value has the same parts in the same order; split into rows when the number of parts varies and each part is a value of the same kind. (Meridian's view)

Sources

  1. [1]Column editing | OpenRefinedocumentation
  2. [2]Delimiter - Wikipediasecondary source
  3. [3]Split a Field into Multiple Fields - Tableaudocumentation
  4. [4]GREL functions | OpenRefinedocumentation
  5. [5]Text Functions – DuckDBdocumentation
  6. [6]Cell editing | OpenRefinedocumentation
  7. [7]Flat-file database - Wikipediasecondary source
  8. [8]Splitter functions - PowerQuery M | Microsoft Learndocumentation
  9. [9]PostgreSQL: Documentation: 18: 9.4. String Functions and Operatorsdocumentation

Used in

3 Protocols use this operation: