Shape / columns · All operations

Take part of a text

A variant of Clean up text

Keep the characters of a text between two positions, or its first or last N characters.

Status

In arcform

Runs as a SQL step

arcform carries it as a sql: step today.

The DuckDB step

Keep the characters between two positions, or the first or last few

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, substring(customer, 2, 4) AS middle, left(customer, 3) AS first3, right(customer, 2) AS last2 FROM orders;
SELECT * FROM result;
SELECT CASE WHEN ((SELECT [middle, first3, last2] FROM result WHERE order_id = 1) = ['lice', 'Ali', 'Ng'] AND (SELECT [middle, first3, last2] FROM result WHERE order_id = 6) = ['ev P', 'Dev', 'el']) THEN 'as expected' ELSE error('Alice Ng and Dev Patel were not cut to their middle four, first three and last two characters') END AS verdict;
  • It needs nothing installed beyond DuckDB itself.
  • The statement checks its own result and raises an error when it is wrong: “Alice Ng and Dev Patel were not cut to their middle four, first three and last two characters”.
  • 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).

Ibis (3)
  • StrRightread 2026-09-27

    Take the last N characters of a string

  • StringSliceread 2026-09-27

    Extract part of a string by start and end position

  • Substringread 2026-09-27

    Extract part of a string by start position and length

OpenRefine (1)
  • Substringread 2026-09-27

    3 functions: substring, slice, get

Polars (1)
RapidMiner Studio (1)
  • Cutread 2026-09-27

    Replace nominal values with the substring between a first and last character index

Background

A substring is a contiguous sequence of characters within a string, and prefixes and suffixes are the substrings at its start and its end.[1] In DuckDB, substring counts from 1, so substring('Hello', 2, 2) returns el.[2] DuckDB's left and right extract a given number of characters from the start or the end of a string.[2]

When to use it

In DuckDB, length counts the characters in a string while strlen counts its bytes, so strlen of a single duck emoji is 4.[2] DuckDB's left_grapheme, right_grapheme and substring_grapheme count grapheme clusters, so left_grapheme of two face-palm emoji with a count of 1 returns the whole first emoji.[2] Cut by grapheme cluster when the text may hold emoji or letters built from combining marks, and by character when it holds codes of a known, plain alphabet. (Meridian's view)

In PostgreSQL, left and right take a negative count to return all but the last or the first characters of a string.[3]

The method

A string u is a substring of a string t when two strings p and s exist such that t is p followed by u followed by s, so the empty string is a substring of every string.[1] PostgreSQL's substring('Thomas' from 2 for 3) returns hom, and with only a FOR count of 2 it returns Th.[3]

Compared with the alternatives

DuckDB's split_part splits a string at a separator and returns the field at a 1-based index, or an empty string when the index is out of range.[2]

Sources

  1. [1]Substring - Wikipediasecondary source
  2. [2]Text Functions – DuckDBdocumentation
  3. [3]PostgreSQL: Documentation: 18: 9.4. String Functions and Operatorsdocumentation