Shape / columns · All operations

Trim and collapse whitespace

A variant of Clean up text

Remove spaces, line breaks or given characters from the ends of text and collapse runs of whitespace inside it.

Status

In arcform

Runs as a SQL step

arcform carries it as a sql: step today.

The DuckDB step

Trim the ends and collapse runs of whitespace

SQL
CREATE TEMP TABLE raw AS SELECT * FROM (VALUES
  (1, '  Bob   Okafor  '), (2, '**ID-7**'), (3, E'a \n  b')
) t(id, s);
CREATE TEMP TABLE result AS
SELECT id, trim(regexp_replace(s, '\s+', ' ', 'g')) AS collapsed, trim(s, '*') AS unstarred FROM raw;
SELECT * FROM result ORDER BY id;
SELECT CASE WHEN ((SELECT collapsed FROM result WHERE id = 1) = 'Bob Okafor' AND (SELECT unstarred FROM result WHERE id = 2) = 'ID-7' AND (SELECT collapsed FROM result WHERE id = 3) = 'a b') THEN 'as expected' ELSE error('padding was not trimmed, stars were not stripped, or a line break and spaces were not collapsed to one space') END AS verdict;
  • The statement builds its own data.
  • It needs nothing installed beyond DuckDB itself.
  • The statement checks its own result and raises an error when it is wrong: “padding was not trimmed, stars were not stripped, or a line break and spaces were not collapsed to one space”.
  • 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).

Duckle (2)
  • Standardizeread 2026-09-25

    trim, case-normalise and collapse whitespace

  • Trimread 2026-09-25

    trim whitespace

EasyMorph (1)
  • Sanitize textread 2026-09-27

    Strip hidden control characters, extra spaces and line breaks from text

Ibis (3)
  • LStripread 2026-09-27

    Trim whitespace from the start of a string

  • RStripread 2026-09-27

    Trim whitespace from the end of a string

  • Stripread 2026-09-27

    Trim whitespace from both ends of a string

OpenRefine (3)
RapidMiner Studio (1)
  • Trim/Stripread 2026-09-27

    Strip leading and trailing whitespace or given characters from nominal values

Tableau Prep (2)
Trifacta (2)
  • Trim quotesread 2026-09-27

    Remove quotes at the beginning and end of text

  • Trim whitespaceread 2026-09-27

    Remove whitespace at the beginning and end of text

Background

Trimming, or stripping, is a string manipulation that removes leading and trailing whitespace from a string.[1] Space normalisation also replaces each run of whitespace inside a string with a single space.[1] Excel's TRIM removes all spaces from text except single spaces between words.[2]

When to use it

Cell values that should be identical, and look identical, often differ because of space or line-break characters that are invisible to users.[3] Text cells may hold what look like spaces but are tabs, or several spaces in a row.[3] Trim every text column that will be joined on, grouped by or compared, and collapse inner runs of spaces wherever the spacing carries no meaning. (Meridian's view)

The characters that count as whitespace vary: C traditionally counts space, tab, line feed and carriage return, while languages that support Unicode typically include all Unicode space characters.[1] The Unicode Character Database defines twenty-five characters as whitespace.[4] Excel's TRIM was designed to trim the 7-bit ASCII space, character 32, and by itself does not remove the non-breaking space, character 160.[2] In DuckDB, trim, ltrim and rtrim remove only spaces unless a set of characters is given as their second argument.[5] Look for non-breaking spaces before trusting a trimmed column. (Meridian's view)

Python's strip treats its chars argument as a set rather than a prefix or suffix, removing every combination of those characters from the ends.[6] In DuckDB, trim('>>>>test<<', '><') returns test.[5]

The method

The most common variants of trimming remove characters from only the start or the end of a string, and are typically named ltrim and rtrim.[1] Many trim functions take an optional list of characters to remove instead of the default whitespace.[1] PostgreSQL's trim removes the longest string made only of the given characters, a space by default, from the start, the end or both ends, both being the default.[7]

Apache Jakarta's StringUtils has a stripToNull function that returns null instead of an empty string when nothing remains after trimming.[1] Decide what a value that trims to nothing should become, an empty string or a missing value, and apply the choice to the whole column. (Meridian's view)

Sources

  1. [1]Trimming (computer programming) - Wikipediasecondary source
  2. [2]TRIM function | Microsoft Supportdocumentation
  3. [3]Cell editing | OpenRefinedocumentation
  4. [4]Whitespace character - Wikipediasecondary source
  5. [5]Text Functions – DuckDBdocumentation
  6. [6]Built-in Types — Python 3.15.2 documentationdocumentation
  7. [7]PostgreSQL: Documentation: 18: 9.4. String Functions and Operatorsdocumentation