Documentation

    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.

    The live example needs a desktop screen. The result it produces is shown here, and the explanation and settings below work on any screen.

    Result
    idnoteparsed_regex
    1Order ID-104 ready104
    2Order ID-208 delayed208
    3No order yet
    4
    1. Remove the parentheses from Pattern, leaving ID-[0-9]+. The output now contains ID-104 and ID-208, including the prefix.
    2. Change Output mode to Match. The two matching notes produce 1; the other two rows produce 0.
    3. Change Output mode back to Extract, then change If no match to Fail. 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.