Shape / columns · All operations

Remove classes of characters

A variant of Clean up text

Remove every letter, digit, punctuation mark, symbol or space from text.

Status

In arcform

Runs as a SQL step

arcform carries it as a sql: step today.

The DuckDB step

Remove every digit, letter, punctuation mark, symbol or space

SQL
CREATE TEMP TABLE raw AS SELECT 'Ab-12 c_3!$+' AS s;
CREATE TEMP TABLE result AS
SELECT regexp_replace(s, '\pN', '', 'g') AS no_digits, regexp_replace(s, '\pL', '', 'g') AS no_letters,
       regexp_replace(s, '\pP', '', 'g') AS no_punctuation, regexp_replace(s, '\pS', '', 'g') AS no_symbols,
       regexp_replace(s, '\s', '', 'g') AS no_spaces FROM raw;
SELECT * FROM result;
SELECT CASE WHEN ((SELECT [no_digits, no_letters, no_punctuation, no_symbols, no_spaces] FROM result) = ['Ab- c_!$+', '-12 _3!$+', 'Ab12 c3$+', 'Ab-12 c_3!', 'Ab-12c_3!$+']) THEN 'as expected' ELSE error('digits, letters, punctuation, symbols and spaces were not each removed from Ab-12 c_3!$+') 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: “digits, letters, punctuation, symbols and spaces were not each removed from Ab-12 c_3!$+”.
  • 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).

KNIME Analytics Platform (1)
  • String Cleanerread 2026-09-27

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

Tableau Prep (4)
Trifacta (2)

Background

Each Unicode code point is assigned a General Category, such as letter, mark, number, punctuation, symbol or separator.[1] In RE2, the regular expression engine DuckDB uses, \pN, \pL, \pP and \pS match the Unicode classes for numbers, letters, punctuation and symbols.[2][3]

When to use it

Removing a whole class of characters suits a field whose meaning lives in one kind of character: the digits of a phone number, the letters of a name typed with stray symbols. (Meridian's view) In DuckDB, regexp_replace replaces only the first occurrence of the pattern by default.[3]

Unicode's Number category covers more than decimal digits: it includes letter numbers such as Roman numerals and other numbers such as vulgar fractions and superscript digits.[1] In RE2, \d matches only the ASCII digits 0 to 9, while \pN matches the whole Unicode Number class.[2] In RE2, Sc, the currency symbol class, is listed among the Symbol classes, apart from the Punctuation classes.[2] Unicode's connector punctuation category includes the underscore, which regular expression libraries may nevertheless class as a word character.[1] Name the class that must go and look at what else it holds before removing it. (Meridian's view)

The method

Unicode's General Category is one of the character properties that is also defined for unassigned code points and for code points that are not characters.[1] RE2 writes a Unicode class with a one-letter name as \pN, a longer name as \p{Greek}, and the negation of either with a capital P, as in \PN.[2] RE2's \w matches only the ASCII word characters 0 to 9, A to Z, a to z and the underscore.[2] RE2's ASCII class [[:punct:]] is defined by ranges of ASCII characters, among them ! to / and : to @.[2]

Compared with the alternatives

DuckDB's translate replaces each character in its from set with the matching character in its to set, and deletes the extra characters when from is longer than to.[4]

Sources

  1. [1]Unicode character property - Wikipediasecondary source
  2. [2]Syntax · google/re2 Wiki · GitHubdocumentation
  3. [3]Regular Expressions – DuckDBdocumentation
  4. [4]Text Functions – DuckDBdocumentation