Using Regex to Extract Patterns from Text

Your data has a column called "address" and the values look like this: "Ship to: 123 Oak Lane, Portland, OR 97201". You need the zip code. Or you have a "notes" column full of strings like "Ref: INV-2024-0456" and you need to pull out those invoice numbers.

The data is there. It's just buried in free text. You could export to a script, write some Python, and re-import. Or you could extract it in place with a regular expression.

ExploreMyData has two operations for this, and picking the right one is most of the battle: Extract Text in regex mode, and Regex Capture. Both use DuckDB's REGEXP_EXTRACT() underneath, and they differ in exactly one way that matters.

Extract Text gives you the whole match

Click the green + in the Pipeline panel, select Extract Text from the Transform group, choose a text column, set Extraction method to regex, and type a pattern into the Regex pattern box.

It runs REGEXP_EXTRACT(column, pattern, 0) on every row. That trailing zero is the important part. Group 0 in regex means "the entire matched text", so Extract Text always returns the full match, and any capture groups you put in the pattern are ignored for the output. Parenthesise all you like; the parentheses will group and alternate as usual, but the column still gets the whole match.

If you want the contents of a group, that's the other operation, and there's a section on it below.

Non-matching rows get an empty string, not NULL, and not an error. More on why that distinction bites people at the end of this post.

Pattern 1: Extract a 5-digit zip code

Given an address like "123 Oak Lane, Portland, OR 97201", you want the zip.

Pattern: \d{5}

This matches exactly five consecutive digits. For most US address data, that's the zip code. It'll grab the first five-digit sequence it finds, so if the street number happens to be five digits ("12345 Main St, Portland, OR 97201"), you'd get "12345" instead of "97201".

A safer version is \b\d{5}\b, using word boundaries. What you cannot do here is write [A-Z]{2}\s+(\d{5}) and expect just the zip: Extract Text returns the whole match, so you'd get "OR 97201". Either write a pattern whose full match is only what you want, or switch to Regex Capture.

Pattern 2: Extract an invoice number

Your notes field contains "Ref: INV-2024-0456" and you want "INV-2024-0456".

Pattern: INV-\d{4}-\d{4}

This matches the literal text "INV-", followed by exactly four digits, a dash, and four more digits. Adjust the prefix and digit counts to match your actual format. If your invoices look like "PO-123456", the pattern would be PO-\d+.

Pattern 3: Extract an email address

A "contact_info" column contains mixed text: "John Smith, john.smith@acme.com, ext 4421". You want the email.

Pattern: [\w.]+@[\w.]+

This matches one or more word characters or dots, then @, then more word characters or dots. It's not a perfect email validator (nothing is, really) but it works for extraction from semi-structured text. It'll grab "john.smith@acme.com" cleanly.

Pattern 4: Extract a dollar amount

Transaction descriptions like "Payment of $1,234.56 received" and you need the numeric amount.

Pattern: \$[\d,]+\.?\d*

This matches a literal dollar sign, one or more digits or commas, an optional decimal point, and optional trailing digits. You'll get "$1,234.56" as a string. To use it numerically, follow up with a Find & Replace to strip the "$" and commas, then Convert Type to DOUBLE.

Tempting shortcut that doesn't work: writing \$([\d,]+\.?\d*) and expecting the dollar sign to be left behind. Extract Text takes the full match, parentheses or not, so you still get "$1,234.56". To drop the sign, either write a pattern that doesn't match it in the first place using a lookbehind, or use Regex Capture, which is what the next section is for.

Two notes for this particular case. Convert Type on a text column already strips $, €, £, ₹, commas and percent signs before casting, so "$1,234.56" converts to 1234.56 without any cleanup step. And if all you need is the first run of digits, the "contains number" extraction method does that without a pattern at all.

Pattern 5: Regex Capture, when you want part of a match

Everything so far returns the whole match. When you want a piece of it, or several pieces at once, reach for the second operation: click the green + and select Regex Capture from the Transform group. Its description in the picker is literally "Extract regex capture groups into new columns", which is the giveaway.

The panel is different from Extract Text. You pick a source column, type one pattern, then define one row per group: a Group # and the output column name it should land in. Click "Add capture group" for as many as your pattern has.

Take an address column holding "123 Oak Lane, Portland, OR 97201" and say you want the state and the zip as two separate columns.

Pattern: ,\s+([A-Z]{2})\s+(\d{5})

• Group 1 → state
• Group 2 → zip

That generates one REGEXP_EXTRACT per group, each with its own group index:

SELECT *,
  REGEXP_EXTRACT("address", ',\s+([A-Z]{2})\s+(\d{5})', 1) AS "state",
  REGEXP_EXTRACT("address", ',\s+([A-Z]{2})\s+(\d{5})', 2) AS "zip"
FROM "orders"

The same pattern through Extract Text would have given you a single column containing ", OR 97201". That is the entire difference between the two operations, and it's worth internalising because the symptom of getting it wrong is not an error, just a column with more text in it than you expected.

Group numbering follows the opening parentheses left to right. Group 1 is the first (, group 2 the second, and so on. Nest them and the outer one is still numbered first.

The generated SQL

Extract Text in regex mode always passes group 0:

REGEXP_EXTRACT("address", '\d{5}', 0) AS "address_extract"

Regex Capture passes the group number you assigned:

REGEXP_EXTRACT("notes", 'INV-(\d{4}-\d{4})', 1) AS "invoice_no"

Hit "Show SQL" on the step card to read it. If the SQL looks right but results are wrong, the issue is the pattern. If the SQL ends in , 0) when you expected a group, you're on the wrong operation.

Regex gotchas to know about

Backslashes in DuckDB. DuckDB regex uses standard syntax. \d means "digit", \w means "word character", \s means "whitespace". Enter these directly in the pattern field. ExploreMyData handles the escaping.

First match only. REGEXP_EXTRACT returns the first match in each cell. If an address has two zip codes ("Ship from 10001 to 97201"), you'll get "10001". Reorder your pattern to be more specific if needed.

Case sensitivity. Regex is case-sensitive by default. If your invoice numbers might be "inv-2024-0456" or "INV-2024-0456", use [Ii][Nn][Vv]-\d{4}-\d{4} or apply a Text Transform (uppercase) before extracting.

Empty, not NULL. This is the one that costs people an afternoon. A row where the pattern doesn't match gets an empty string, not NULL. So a filter looking for NULLs finds nothing at all and you conclude, wrongly, that every row matched.

To audit what didn't match, add a Filter on the extracted column with the is Empty operator. That one is written to catch both cases, generating (col IS NULL OR col = ''), so it works whether the blanks came from a failed match or from a NULL in the source. Its opposite, "is NOT Empty", gives you the rows that did match.

Extract patterns from your data →