RegEx
Extract useful text, such as an order number inside a note, or flag values that match a pattern. Use parentheses to choose the part to extract, or Match mode to add a 1 or 0 flag.
Extract order numbers
Manual Input has four rows with id and note columns. RegEx uses ID-([0-9]+) to extract the digits after ID- into parsed_regex.
RegEx interactive example
Loading interactive example…
The live example needs a desktop screen. The result it produces is shown here, and the explanation and settings below work on any screen.
| id | note | parsed_regex |
|---|---|---|
| 1 | Order ID-104 ready | 104 |
| 2 | Order ID-208 delayed | 208 |
| 3 | No order yet | |
| 4 |
- Remove the parentheses from Pattern, leaving
ID-[0-9]+. The output now containsID-104andID-208, including the prefix. - Change Output mode to
Match. The two matching notes produce 1; the other two rows produce 0. - Change Output mode back to
Extract, then change If no match toFail. No order yet causes an error.
When to use
Use RegEx to pull a piece out of text, such as an order number inside a note, or to flag values matching a pattern. If the text splits cleanly on a separator, use Split to columns.
Read the output
The output keeps id and note and adds parsed_regex. Order ID-104 ready produces 104 and Order ID-208 delayed produces 208. These are text values, even though they contain only digits. No order yet and the missing note produce NULL. All four rows stay.
In Match mode, the same pattern produces 1, 1, 0 and 0. These flags identify matching rows; they don't filter the table.
Read the pattern
In ID-([0-9]+), ID- matches that exact prefix. [0-9] matches a digit, and + means one or more. The parentheses capture just the digits, leaving the prefix out of the result.
Without parentheses, ID-[0-9]+ returns the whole match, such as ID-104. To ignore letter case, start the pattern with (?i), for example (?i)ID-([0-9]+).
To convert extracted numeric text into numbers, use Number.
To separate text using a fixed delimiter, use Split to columns.
Inputs and outputs
Inputs
1 port- InRequired
Accepts: Tables, Geospatial data.
Accepts one connection on this port.
Outputs
1 port- Out
Configure the block
Choose the text to search, the pattern to find and whether to extract text or flag a match.
- Column
Choose the text column to search. The first text column is selected automatically.
- Pattern
Enter a regular expression. Parentheses capture the part to return in Extract mode. Matching is case-sensitive by default.
- Output mode
Extract returns the first captured group, falling back to the whole match if that group is absent or empty. Match returns 1 for a match and 0 otherwise. Default: Extract.
- Output column
Name the result column. Use the source column's name to replace it. Default: parsed_regex.
- If no match
Shown for Extract. Null leaves a nonmatching value missing; Fail reports an error. This setting doesn't apply to Match mode. Default: Null.
RegEx FAQs
- Does the pattern have to match the entire value?
- No. It can match anywhere in the text. Use ^ at the start and $ at the end to require a match across the whole value.
- What happens to missing values?
- Extract returns NULL for a NULL input, even with Fail selected. Match returns 0. Neither mode removes rows.
- Can it extract several matches into separate columns?
- No. Extract returns one value from the first match. If the pattern has several capture groups, only the first is returned.
Prepare your data. Build it in Strata.
Strata opens in your browser, with no account.