Shape / columns · All operations

Extract with a regular expression

A variant of Match with a regular expression

Copy the parts of a value that match a pattern, or its capture groups, into new columns or an array.

Status

In arcform

Runs as a SQL step

arcform carries it as a sql: step today.

The DuckDB step

Copy a capture group, and every match, out of a value

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_extract(meta, '"channel":"(\w+)"', 1) AS channel, regexp_extract_all(meta, '\d+') AS item_ids FROM orders;
SELECT * FROM result;
SELECT CASE WHEN ((SELECT count(*) FILTER (WHERE channel = 'web') FROM result) = 6 AND (SELECT count(*) FILTER (WHERE channel = 'phone') FROM result) = 2 AND (SELECT item_ids FROM result WHERE order_id = 3) = ['4', '5', '6'] AND (SELECT sum(len(item_ids)) FROM result) = 15) THEN 'as expected' ELSE error('the channel group and the item numbers were not pulled out of the JSON text') END AS verdict;
  • It needs nothing installed beyond DuckDB itself.
  • The statement checks its own result and raises an error when it is wrong: “the channel group and the item numbers were not pulled out of the JSON text”.
  • 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 Hop (1)
  • Regex Evaluationread 2026-09-27

    Match a field against a regular expression and extract capture groups to new fields

Apache NiFi (2)
  • ExtractGrokread 2026-09-27

    Match content against Grok expressions; results go to attributes or replace content as JSON

  • ExtractTextread 2026-09-27

    Evaluate regular expressions against content and write the matches to attributes

Apache SeaTunnel (1)
  • RegexExtractread 2026-09-27

    Extract regex capture groups from a field into new fields, with defaults when nothing matches

AWS Glue (1)
Duckle (1)
  • Regex Extractread 2026-09-25

    extract a capture group with regexp_extract

EasyMorph (1)
  • Regular expressionread 2026-09-27

    Add a column holding the regular-expression matches found in a text column

Ibis (2)
  • RegexExtractread 2026-09-27

    Extract a capture group of a regular expression from a string

  • RegexSplitread 2026-09-27

    Split a string into an array at matches of a regular expression

KNIME Analytics Platform (1)
  • String Splitter (Regex)read 2026-09-27

    Split a string column into columns, one per capture group of a regular expression

Pentaho Data Integration (1)
  • Regex Evaluationread 2026-09-27

    Test a field against a regular expression and extract capture groups to new fields

RapidMiner Studio (1)
  • Keep Document Partsread 2026-09-27

    Keep only the first part of each token that matches a regular expression

Talend (3)
  • tExtractRegexFieldsread 2026-09-27

    Split a formatted string column into several columns by regex matching

  • tPatternExtractread 2026-09-27

    Extract from a column the strings that match a Java regular expression

  • tStandardizeRowread 2026-09-27

    Tokenise text and apply parser rules, writing the tagged and standardised parts as XML or JSON

Trifacta (6)
VisiData (2)
  • addcol-captureread 2026-09-23

    Add column from regex capture groups of current column

  • capture-colread 2026-09-23

    (deprecated) Add columns from regex capture groups of current column

Background

Parentheses in a regular expression define a capturing group, which is essential for extracting the desired part of the text a pattern matches.[1] After a regular expression with parenthesised subexpressions matches a string, many regular expression engines make the text matched by each one available.[2] In DuckDB, regexp_extract returns the capturing group asked for when the string contains the pattern.[3]

When to use it

In DuckDB, regexp_extract returns the empty string when the string does not contain the pattern.[3] In PostgreSQL, regexp_substr returns the substring that matches a regular expression, or NULL if there is no match.[4] Before counting the values that did not match, check what the engine returns on a miss, and turn it into NULL where the column must tell a miss from an empty capture. (Meridian's view)

In DuckDB, regexp_extract_all finds the non-overlapping occurrences of a pattern in a string and returns the chosen group from each.[3] In Python, re.findall returns a list of strings when the pattern has no group or exactly one group, and a list of tuples when several groups are present.[5] Decide whether a value can hold one match or several before choosing the function, so that later matches are not lost to a function that reads only the first. (Meridian's view)

In PostgreSQL, substring(string from pattern) returns the first portion of text that matched, but if the pattern contains any parentheses it returns the part matched by the first parenthesised subexpression.[4] Wrap the parts of a pattern that are there only for grouping in non-capturing parentheses, so that the group numbers stay on the parts to be extracted. (Meridian's view) In DuckDB, regexp_extract with a list of names returns only the first groups if the list is shorter than the pattern's capture groups, and raises an error if it is longer.[3]

The method

When regular expressions are used for splitting or parsing strings, submatch extraction finds out which sections of the input string were matched by each subexpression, such as a date and a time.[2] The extraction of submatch boundaries has been mostly ignored by computer science theorists, and is perhaps the most compelling argument for using recursive backtracking.[2]

In DuckDB, regexp_extract given a list of names returns the capturing groups as fields of a STRUCT, so that '2023-04-15' with three groups named y, m and d gives the year, month and day.[3] In DuckDB, regexp_extract_all given a list of names returns the capturing groups of each match as a list of structs.[3]

Compared with the alternatives

In DuckDB, regexp_split_to_array splits a string along a regular expression, so that splitting 'hello world; 42' on ';? ' gives the list hello, world and 42.[3] Extract with groups when you know what the wanted text looks like, and split when you know what the separators look like. (Meridian's view)

Sources

  1. [1]Regular expression - Wikipediasecondary source
  2. [2]Regular Expression Matching Can Be Simple And Fastsecondary source
  3. [3]Regular Expressions – DuckDBdocumentation
  4. [4]PostgreSQL: Documentation: 18: 9.7. Pattern Matchingdocumentation
  5. [5]re — Regular expression operations — Python 3.15.2 documentationdocumentation