Shape / columns · All operations

Replace with a regular expression

A variant of Match with a regular expression

Replace or remove the parts of a value that match a pattern, optionally using backreferences.

Status

In arcform

Runs as a SQL step

arcform carries it as a sql: step today.

The DuckDB step

Replace the parts a pattern matches, with a backreference

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, regexp_replace(customer, '^(\S+)\s+(\S+)$', '\2, \1') AS surname_first, regexp_replace(product, '[aeiou]', '', 'g') AS consonants FROM orders;
SELECT * FROM result;
SELECT CASE WHEN ((SELECT surname_first FROM result WHERE order_id = 3) = 'Okafor, Bob' AND (SELECT surname_first FROM result WHERE order_id = 1) = 'Ng, Alice' AND (SELECT list(DISTINCT consonants ORDER BY consonants) FROM result) = ['gdgt', 'gzm', 'wdgt']) THEN 'as expected' ELSE error('names were not turned surname first, or vowels were not removed from the products') END AS verdict;
  • It needs nothing installed beyond DuckDB itself.
  • The statement checks its own result and raises an error when it is wrong: “names were not turned surname first, or vowels were not removed from the products”.
  • 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 NiFi (1)
  • ReplaceTextread 2026-09-27

    Replace content matched by a regular expression or literal with a replacement value

Duckle (1)
Ibis (1)
  • RegexReplaceread 2026-09-27

    Replace matches of a regular expression in a string

OpenRefine (1)
  • Find and replaceread 2026-09-27

    7 functions: replace, replaceChars, replaceEach, find, match

RapidMiner Studio (4)
  • Remove Document Partsread 2026-09-27

    Delete the parts of a document that match a regular expression

  • Replaceread 2026-09-27

    Replace the parts of nominal values matching a regular expression

  • Replace (In Database)read 2026-09-27

    Replace the parts of string values matching a regular expression, executed in the database

  • Replace Tokensread 2026-09-27

    Regex find-and-replace within each token

VisiData (6)
  • addcol-regex-substread 2026-09-23

    Add column replacing regex matches in current column, with backreferences

  • addcol-substread 2026-09-23

    (deprecated) Add column replacing regex matches in current column

  • setcol-regex-substread 2026-09-23

    Replace regex matches in current column for selected rows

  • setcol-regex-subst-allread 2026-09-23

    Replace regex matches in all visible columns for selected rows

  • setcol-substread 2026-09-23

    (deprecated) Replace regex matches in current column for selected rows

  • setcol-subst-allread 2026-09-23

    (deprecated) Replace regex matches in all visible columns for selected rows

Background

In PostgreSQL, regexp_replace substitutes new text for the substrings that match a regular expression, and returns the string unchanged if nothing matches.[1] In DuckDB, the notation \d in a regexp_replace replacement string, where d is a group's number, refers to the text that capturing group matched.[2] In Python, the replacement given to re.sub can be a function, called for every non-overlapping occurrence of the pattern and returning the replacement string.[3]

When to use it

In DuckDB, regexp_replace replaces only the first occurrence of the regular expression by default, and the global flag g replaces all occurrences.[2] In Python, re.sub replaces all occurrences when its count argument is omitted or zero.[3] Say first or all explicitly in every replacement, so that a pattern carried between a SQL engine and Python keeps doing the same thing. (Meridian's view) To remove the parts a pattern matches, replace them with the empty string, with the global flag wherever every occurrence should go. (Meridian's view)

In DuckDB, regexp_replace('abc', '(a)(b)', '\2\1') returns bac, the two captured groups swapped.[2] RE2 does not support backreferences such as \1 within a pattern, even though DuckDB, which uses RE2, accepts \1 in a replacement string to refer to a captured group.[4][2] In DuckDB, regular expressions support only 9 capture groups, \1 to \9, and capture groups with two or more digits are not supported.[2] In Python, \g<2>0 in a replacement means group 2 followed by the literal character 0, whereas \20 is read as a reference to group 20.[3] In PostgreSQL, \& in a regexp_replace replacement inserts the substring matching the entire pattern, and \\ is written for a literal backslash.[1] Check how the engine reads backslashes in the replacement before running it over a whole column, since a stray backslash or a two-digit group number is easy to misread. (Meridian's view)

The method

In sed, the replacement of the s command can contain references \1 to \9 to the parts of the match between parentheses, and an unescaped & refers to the whole matched portion.[5] In sed, the g flag after the s command applies the replacement to all matches of the regular expression, not just the first.[5] In Perl, the operator =~ s/// marks a regular expression substitution.[6]

In Python, re.sub replaces the leftmost non-overlapping occurrences of the pattern, and returns the string unchanged if the pattern is not found.[3] In PostgreSQL, a regexp_replace search normally begins at the start of the string, but begins at a given character index when the start parameter is provided.[1]

Compared with the alternatives

In PostgreSQL, the plain replace function replaces all occurrences of one substring with another, while regexp_replace replaces only the first match of a regular expression unless the g flag is used.[7]

Sources

  1. [1]PostgreSQL: Documentation: 18: 9.7. Pattern Matchingdocumentation
  2. [2]Regular Expressions – DuckDBdocumentation
  3. [3]re — Regular expression operations — Python 3.15.2 documentationdocumentation
  4. [4]Syntax · google/re2 Wiki · GitHubdocumentation
  5. [5]The "s" Command (sed, a stream editor)documentation
  6. [6]Regular expression - Wikipediasecondary source
  7. [7]PostgreSQL: Documentation: 18: 9.4. String Functions and Operatorsdocumentation