Regex Capture

Split one coded column into several clean columns, in a single step.

The idea in one minute

A pattern describes the shape of a value. Put brackets around each part you want to keep. Each bracketed part is a capture group. The panel turns each group into its own column.

Take a warehouse file with a shipment code like MUM-2026-0043. One pattern splits it into a hub, a year and a serial number. Extract Text would need three separate steps.

Extract the parts

  1. Type regex in the Search transforms box, in the Pipeline panel.
  2. Select Regex Capture in the results.
  3. Open Source column and pick the coded column.
  4. Type your pattern in Regex pattern. For the code above, type ([A-Z]+)-(\d{4})-(\d+).
  5. Look at Capture groups → output columns. One row is ready.
  6. Leave Group # at 1. Type hub in the name box beside it.
  7. Click Add capture group. The new row gets Group # 2. Name it year.
  8. Click Add capture group once more. Name group 3 serial.
  9. Click Apply.

Group numbers count the opening brackets from left to right. Group 1 is the first bracket in your pattern.

What comes out

shipment_codehubyearserial
MUM-2026-0043MUM20260043
DEL-2025-0917DEL20250917
pending assignment(empty)(empty)(empty)

The last row does not fit the pattern. Its three new cells stay empty. No row is ever removed. The source column stays in the table.

Pattern pieces you will use most

PieceMeaning
\dAny one digit.
\wAny one letter, digit or underscore.
.Any one character.
+One or more of the piece before it.
*Zero or more of the piece before it.
{4}Exactly four of the piece before it.
[A-Z]Any one capital letter.
( )Keep this part as a capture group.

More patterns to copy:

  • Email name and domain: (\w+)@([\w.]+)
  • Price and currency from USD 45.90: ([A-Z]{3}) ([\d.]+)
  • Post code and suffix from 560001-4021: (\d{6})-(\d{4})

Rules the panel checks

  • Pick a source column, or the step reports a missing column.
  • Type a pattern. An empty pattern is rejected.
  • Name every group you add. A row with no name is ignored, and all-empty names stop the step.
  • Use a different name for each group. Two identical names stop the step.

The SQL it runs

Three groups produce three calls on the same pattern:

SELECT *, REGEXP_EXTRACT("shipment_code", '([A-Z]+)-(\d{4})-(\d+)', 1) AS "hub", REGEXP_EXTRACT("shipment_code", '([A-Z]+)-(\d{4})-(\d+)', 2) AS "year", REGEXP_EXTRACT("shipment_code", '([A-Z]+)-(\d{4})-(\d+)', 3) AS "serial" FROM data
Try Regex Capture with sample data →

Related Operations