ETL: REGEX_EXTRACT

Extract matching text from a string using a regular expression, returning the nth occurrence by index. Use for pattern-based parsing in ETL jobs.

Description

Returns the nth occurrence (specified by index) of a string that matches the regular expression.

Syntax

REGEX_EXTRACT(string_expression, regex, index)

Arguments

string_expression - string expression

regex - string expression indicating a regular expression

index - integer indicating the occurrence

Examples

REGEX_EXTRACT('213.131.343.135:5020', '(.*)\\:(.*)', 1) returns ‘213.131.343.135’

Notes

• The regex string must be enclosed by single quotes.
• If string matching fails, null is returned.
• Make sure to escape a single-quotes (i.e. \’ )
• Make sure to escape back-slashes that are part of a regular expression escape sequence (e.g. instead of \s as shorthand for whitespace, use \\s )

Return value datatype

String

Impact of null value

If input is null, returns null.