Shape / columns · All operations

Split a column at character positions

A variant of Split a column

Split or cut a text column at fixed widths or given character positions.

Status

In arcform

Runs as a SQL step

arcform carries it as a sql: step today.

The DuckDB step

Cut a column at character positions

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, substr(order_date::VARCHAR, 1, 4) AS year, substr(order_date::VARCHAR, 6, 2) AS month, substr(order_date::VARCHAR, 9, 2) AS day FROM orders;
SELECT * FROM result;
SELECT CASE WHEN ((SELECT [year, month, day] FROM result WHERE order_id = 10) = ['2026', '02', '10'] AND (SELECT [year, month, day] FROM result WHERE order_id = 1) = ['2026', '01', '03'] AND (SELECT count(DISTINCT month) FROM result) = 2) THEN 'as expected' ELSE error('the date text was not cut into year 2026, month and day at positions 1, 6 and 9') 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 date text was not cut into year 2026, month and day at positions 1, 6 and 9”.
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)

String cutread 2026-09-27
Extract a substring by start and end position

EasyMorph (1)

Split fixed width textread 2026-09-27
Split a text column at fixed character widths into new columns

KNIME Analytics Platform (1)

Cell Splitter By Positionread 2026-09-27
Split a column at fixed character positions into several columns

Pentaho Data Integration (1)

Strings cutread 2026-09-27
Cut a substring out of a string

Talend (1)

tExtractPositionalFieldsread 2026-09-27
Split a string column into several columns by character position

Trifacta (6)

Extract between positionsread 2026-09-27
Extract the characters between two positions into a new column
Extract firstread 2026-09-27
Extract the first n characters of each value into a new column
Extract lastread 2026-09-27
Extract the last n characters of each value into a new column
Split at intervalread 2026-09-27
Split a column at a regular character interval
Split at positionsread 2026-09-27
Split a column at given character positions
Split between positionsread 2026-09-27
Split a column on the span between two character positions

Background

In Power Query, Splitter.SplitTextByPositions returns a function that splits text into a list of text at each specified position.^[1] A substring is a contiguous sequence of characters within a string.^[2] With fixed-width formats, each field has a fixed length with extra spaces added as needed.^[3] In DuckDB, substring extracts characters from a start position, where a start value of 1 refers to the first character of the string.^[4]

When to use it

In Tableau Prep, the Split recommendation works specifically with data in fixed-width text files, loaded as a single field by choosing a separator the data does not use.^[5] In OpenRefine, splitting by lengths can be useful when cells hold predictable data, such as a 10-digit phone number followed by a space and another 10-digit phone number.^[6] In Tableau, the LEFT and RIGHT functions suit extracting a specific part of a string, by a number of characters, rather than splitting on a separator.^[7]

In OpenRefine, splitting by lengths discards any characters past the total length given, so a split by 11 and 10 loses everything after the 21st character.^[6] In fixed-width formats with predefined lengths, fields are limited to a maximum length, and the need for longer fields may appear after the format is defined.^[3] Trim each part after cutting, unless its spaces carry meaning. (Meridian's view)

In DuckDB, length counts the characters in a string while strlen counts its bytes, and a duck emoji is 4 bytes.^[4] Check whether a layout's positions count characters or bytes before cutting text that holds accented letters or emoji. (Meridian's view)

The method

In fixed-width formats, the field lengths can be predefined in the format's specification or parsed from a header.^[3] In Power Query, Splitter.SplitTextByLengths splits text by each specified length, so lengths 2 and 3 split AB123 into AB and 123.^[8] Power Query's splitters by positions and by lengths both take an optional startAtEnd argument that counts from the end of the input.^[1]^[8] A layout is easier to check written as lengths, and easier to read against a file specification written as start positions; either converts to the other by a running total. (Meridian's view)

OpenRefine's splitByLengths function returns the substrings of the given lengths, so lengths 5, 6 and 3 split internationalization into inter, nation and ali.^[9] In PostgreSQL, right returns the last n characters of a string, or all but the first n characters when n is negative.^[10]

Compared with the alternatives

With delimiter-separated formats, finding the field boundaries means finding the delimiters, which incurs some computational overhead that fixed-width formats do not need.^[3] Fixed-width formats can lead to unnecessarily large files when fields tend to be shorter than the lengths reserved for them.^[3] Comma-separated value lists are easier to type into punched cards than fixed-column-aligned data, and less prone to wrong results when a value was punched one column off.^[11]

Sources

[1]Splitter.SplitTextByPositions - PowerQuery M | Microsoft Learndocumentation
[2]Substring - Wikipediasecondary source
[3]Flat-file database - Wikipediasecondary source
[4]Text Functions – DuckDBdocumentation
[5]Clean and Shape Data in Tableau Prep - Tableaudocumentation
[6]Cell editing | OpenRefinedocumentation
[7]Split a Field into Multiple Fields - Tableaudocumentation
[8]Splitter.SplitTextByLengths - PowerQuery M | Microsoft Learndocumentation
[9]GREL functions | OpenRefinedocumentation
[10]PostgreSQL: Documentation: 18: 9.4. String Functions and Operatorsdocumentation
[11]Comma-separated values - Wikipediasecondary source