REGEX

Description

REGEX

is a row function that performs a whole string match against a

TEXT

value with a regular expression and returns the portion of the string matching the first capturing group of the regular expression.

Syntax

REGEX

(

string_expression

regex_matching_pattern

Return Value

Returns the matched

TEXT

value of the first capturing group of the regular expression. If there is no match, returns

NULL

Input Parameters

string_expression: Required. The name of a field or expression of type
TEXT
(or a literal string).
regex_matching_pattern: Required. A regular expression pattern based on the regular expression pattern matching syntax of the Java programming language. To return a non-NULL value, the regular expression pattern must match the entire
TEXT
value.

Regular Expression Constructs

See the Regular Expression Reference for information on the constructs used for defining a regular expression matching pattern.

Capturing and Non-Capturing Groups

Groups are specified by a pair of parenthesis around a subpattern in the regular expression. A pattern can have more than one group and the groups can be nested. The groups are numbered 1-n
from left to right, starting with the first opening parenthesis. There is always an implicit group 0, which contains the entire match. For example, the pattern:

(a(b*))+(c)

contains three groups:

group 1: (a(b*))
group 2: (b*)
group 3: (c)

Capturing Groups

By default, a group captures
the text that produces a match, and only the most recent match is captured. The

REGEX

function returns the string that matches the first capturing group in the regular expression. For example, if the input string to the expression above was

abc

, the entire

REGEX

function would match to

abc

, but only return the result of group 1, which is

Non-Capturing Groups

In some cases, you may want to use parenthesis to group subpatterns, but not capture text. A non-capturing group starts with

(?:

(a question mark and colon following the opening parenthesis). For example,

h(?:a|i|o)t

matches

hat

hit

hot

, but does not capture the

, or

from the subexpression.

Examples

Match all possible email addresses with a pattern of username
@provider
.
domain
, but only return the provider
portion of the email address from the email
field:

REGEX([email], "^[a-zA-Z0-9._%+-]+@([a-zA-Z0-9._-]+)
\.[a-zA-Z]{2,4}$")

Match the request line of a web log, where the value is in the format of: