Shape / columns · All operations

Clean up text

String functions: trim, case, pad, substring, remove characters, normalise whitespace or unicode.

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

Trim, fold case and accents, collapse spaces

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, regexp_replace(trim(lower(strip_accents(customer))), '\s+', ' ', 'g') AS cleaned FROM orders;
SELECT * FROM result;
SELECT CASE WHEN ((SELECT count(DISTINCT customer) = 8 AND count(DISTINCT cleaned) = 5 FROM result)) THEN 'as expected' ELSE error('eight spellings did not clean to five names') END AS verdict;
  • It needs nothing installed beyond DuckDB itself.
  • The statement checks its own result and raises an error when it is wrong: “eight spellings did not clean to five names”.
  • It passed on DuckDB v2.0.0 (Cyanoptera) d54cbbe3df.

Variants

Other text functions

Use a library of text functions, a multi-step cleaner, or a text function no other variant covers.

Standardise phone numbers

Rewrite phone numbers in one standard format, checking that each is valid.

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 (2)
  • Data Cleanse Proread 2026-09-27

    Fix common quality issues: replace nulls, strip punctuation and HTML tags, change case

  • Data Cleansingread 2026-09-27

    Fix common quality issues: replace nulls, strip punctuation and whitespace, change case

Apache DataFusion (1)
  • String Functionsread 2026-09-27

    40 functions, 7 of them aliases: concat, lower, trim, substr, split_part, …

Apache Hop (3)
  • HTML 2 Textread 2026-09-27

    Strip HTML markup from a field, leaving the text content

  • Standardize Phone Numberread 2026-09-27

    Normalise phone numbers to a standard format and report type and validity

  • String operationsread 2026-09-27

    Trim, change case, pad, escape, strip digits or special characters in string fields

Apache NiFi (1)
  • String Manipulationread 2026-09-27

    25 functions: toUpper, toLower, trim, substring, substringBefore, …

Apache SeaTunnel (1)
  • String Functionsread 2026-09-27

    32 functions: ASCII, BIT_LENGTH, CHAR_LENGTH / LENGTH, OCTET_LENGTH, CHAR / CHR, …

AWS Glue (2)
  • FormatCaseread 2026-09-27

    Change each string of a column to a given case

  • FormatPhoneNumberread 2026-09-27

    Convert a phone number string into a formatted value

Azure Data Factory (1)
  • String functionsread 2026-09-27

    12 functions: concat, endsWith, guid, indexOf, lastIndexOf, …

Bruin (1)
dbt-utils (3)
  • String functionsread 2026-09-27

    7 macros: concat, hash, length, position, replace (also right, split_part)

  • String literal functionsread 2026-09-27

    2 macros: escape_single_quotes, string_literal

  • slugifyread 2026-09-27

    Turn a Jinja string into a lowercase underscore slug usable as a column name

DuckDB (1)
Duckle (6)
  • Case Changeread 2026-09-25

    change letter case

  • Pad Stringread 2026-09-25

    left or right pad a string to a fixed length

  • Slugread 2026-09-25

    URL-safe slug: lowercase, hyphens, no punctuation

  • Standardizeread 2026-09-25

    trim, case-normalise and collapse whitespace

  • Strip HTMLread 2026-09-25

    remove HTML tags by regex

  • 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 (21)
  • Capitalizeread 2026-09-27

    Upper-case the first letter of a string

  • EndsWithread 2026-09-27

    Test whether a string ends with a given suffix

  • FindInSetread 2026-09-27

    Find the position of a string within a list of strings

  • LPadread 2026-09-27

    Pad a string on the left to a given length

  • LStripread 2026-09-27

    Trim whitespace from the start of a string

  • Lowercaseread 2026-09-27

    Convert a string to lower case

  • RPadread 2026-09-27

    Pad a string on the right to a given length

  • RStripread 2026-09-27

    Trim whitespace from the end of a string

  • Repeatread 2026-09-27

    Repeat a string a given number of times

  • Reverseread 2026-09-27

    Reverse the characters of a string

  • StartsWithread 2026-09-27

    Test whether a string starts with a given prefix

  • StrRightread 2026-09-27

    Take the last N characters of a string

  • StringAsciiread 2026-09-27

    ASCII code of the first character of a string

  • StringContainsread 2026-09-27

    Test whether a string contains a given substring

  • StringFindread 2026-09-27

    Find the position of a substring within a string

  • StringLengthread 2026-09-27

    Length of a string

  • StringSliceread 2026-09-27

    Extract part of a string by start and end position

  • Stripread 2026-09-27

    Trim whitespace from both ends of a string

  • Substringread 2026-09-27

    Extract part of a string by start position and length

  • Translateread 2026-09-27

    Swap characters in a string by a from-set and a to-set

  • Uppercaseread 2026-09-27

    Convert a string to upper case

KNIME Analytics Platform (2)
  • Case Converterread 2026-09-27

    Convert string columns to upper or lower case

  • String Cleanerread 2026-09-27

    Remove whitespace, punctuation and other characters, change case or pad strings

OpenRefine (11)
Pentaho Data Integration (1)
  • String Operationsread 2026-09-27

    Trim, pad and apply other operations to string values

Polars (3)
Power Query M (1)
  • Text functionsread 2026-09-27

    45 functions: Text.Trim, Text.Proper, Text.PadStart, Text.BetweenDelimiters, Text.Replace

RapidMiner Studio (5)
  • Cutread 2026-09-27

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

  • Transform Casesread 2026-09-27

    Convert all characters of a document to lower or upper case

  • Trim/Stripread 2026-09-27

    Strip leading and trailing whitespace or given characters from nominal values

  • Unescape HTMLread 2026-09-27

    Decode HTML escape sequences in column values

  • Unescape HTML Documentread 2026-09-27

    Decode HTML escape sequences in a document

Tableau Prep (11)
  • Cleanread 2026-09-27

    Menu of quick cleaning operations applied to every value of a text field

  • Make Lowercaseread 2026-09-27

    Change all values to lower case

  • Make Titlecaseread 2026-09-27

    Change all values to title case

  • Make Uppercaseread 2026-09-27

    Change all values to upper case

  • Remove All Spacesread 2026-09-27

    Remove every whitespace character

  • Remove Extra Spacesread 2026-09-27

    Trim and collapse repeated whitespace to a single space

  • Remove Lettersread 2026-09-27

    Remove letters, leaving the other characters

  • Remove Numbersread 2026-09-27

    Remove digits, leaving letters and other characters

  • Remove Punctuationread 2026-09-27

    Remove punctuation

  • String functions (group)read 2026-09-27

    25 functions: CONTAINS, MID, REGEXP_EXTRACT, SPLIT, TRIM; search, substring, regex, case, trim

  • Trim Spacesread 2026-09-27

    Remove leading and trailing spaces

Talend (4)
Trifacta (12)

Background

String functions are used in programming languages to manipulate a string or to query information about one, and some do both.[1] Data cleansing replaces, modifies or deletes the incomplete, incorrect or inaccurate parts of a dataset after detecting them.[2] The inconsistencies that cleansing removes may come from user entry errors, from corruption in transmission or storage, or from differing data definitions.[2]

When to use it

Reach for text clean-up when values that mean the same thing are written differently in ways a machine notices and a reader does not: a stray space, a capital letter, an accent typed two ways. (Meridian's view) In Unicode, the letter n followed by a combining tilde is canonically equivalent to the single character ñ, and the two should be treated alike when searching or alphabetising.[3] Software that searches or compares Unicode strings without taking equivalent code points into account cannot find visually identical text that is written with a different but canonically equivalent sequence.[3]

Decide first whether the cleaned text is a key for matching or a value someone will read, because a matching key can be cleaned far harder than anything shown on a page. (Meridian's view) Write the cleaned text to a new column beside the raw one, so a step that went too far can be seen and undone. (Meridian's view) Each clean-up step changes what the next one sees, so fix the order of the steps and keep it the same every time the data is cleaned. (Meridian's view)

The method

OpenRefine builds its fingerprint key by trimming the ends, lowercasing, removing punctuation and control characters, and reducing extended western characters to ASCII, before sorting the words.[4] In OpenRefine's fingerprint, the order of the operations is significant.[4] OpenRefine's fingerprint ignores whitespace, case and punctuation because they matter least for telling meanings apart and vary most, so removing them helps clusters emerge.[4]

Unicode provides normalisation algorithms that produce one normal code point sequence for all sequences that are equivalent, by canonical or by compatibility criteria.[3]

Compared with the alternatives

In DuckDB, a collation provides rules for how text is sorted or compared, and the stand-alone installation includes the NOCASE, NOACCENT and NFC collations.[5] Pick a collation when only comparisons should ignore a difference, and clean the text when the output itself must change. (Meridian's view) OpenRefine's clustering works only at the syntactic level, the character composition of a value, and is not enough on its own for semantically aware reconciliation.[4] Treat cleaned text as a better key for matching, not as proof that two values name the same thing. (Meridian's view)

Sources

  1. [1]Comparison of programming languages (string functions) - Wikipediasecondary source
  2. [2]Data cleansing - Wikipediasecondary source
  3. [3]Unicode equivalence - Wikipediasecondary source
  4. [4]Clustering Methods In-depth | OpenRefinedocumentation
  5. [5]Collations – DuckDBdocumentation

Used in

5 Protocols use this operation: