Regular Expression Functions

Scalar functions for pattern matching and advanced text processing using regular expressions.

Regex operators

Operator	Meaning	Equivalent
~	Matches, case sensitive	regexp_like(value, pattern)
!~	Does not match, case sensitive	NOT regexp_like(value, pattern)
~*	Matches, case insensitive	Case-insensitive regex match
!~*	Does not match, case insensitive	Negated case-insensitive regex match

FROM events
WHERE table_name ~ r'rbit_cls_cloudtrail_.*'
  AND message !~* r'access denied'

These operators search anywhere in the string. Use ^ and $ to match the whole string: name ~ r'^admin$'. They have the same precedence as comparisons, bind more tightly than NOT, AND, and OR, and return null for null inputs. The starred operators are single tokens: write ~*, with no space inside it.

There is no public flags argument on regexp_like. Use inline flags for other options, such as (?m) for multiline matching or (?s) to let . match newlines.

Literal patterns and validation

Hamelin checks literal pattern arguments to every regexp_* function and these four operators during compilation. Invalid patterns, backreferences inside patterns, and look-around are rejected. Computed patterns are checked by the execution engine at runtime. This validation does not apply to literal-substring functions such as replace.

Raw strings are useful for patterns that match backslashes. For example, regexp_like(path, r'\\') searches for a literal backslash; the ordinary-string form is regexp_like(path, '\\\\'). Both '\d+' and r'\d+' match digits. See raw strings for the literal rules.

Literal patterns use Hamelin's portable regex dialect. Compilation resolves Unicode properties and character classes (including &&, --, and ~~) into explicit character ranges. It also translates U (ungreedy), u (Unicode mode), inline case folding, and both (?<name>...) and (?P<name>...) capture spellings. Named captures still have numeric indexes for extraction. ${name} references a named capture in replacement strings on both DataFusion and Trino. This shared form does not make all replacement syntax portable; see replacement strings.

The compiler rejects constructs without a supported portable translation: CRLF flag R, directional/half word boundaries (\b{start}, \b{end}, \b{start-half}, \b{end-half}, \<, \>), and ASCII-only word boundaries. Ordinary Unicode \b and \B remain available. Stacked quantifiers such as a++ are rejected because engines interpret them differently; explicitly grouped repetitions such as (?:a+)+ are accepted.

Case folding uses Unicode simple folding, and $ without multiline mode means the absolute end of the string. These rules apply to literal patterns on every target. Case-insensitive operators respect scoped overrides such as (?-i:...).

Computed patterns still go directly to the execution engine at runtime and do not yet receive this normalization. Their syntax follows DataFusion or Trino, depending on the backend. Replacement-string syntax and iteration over empty matches still retain backend differences. Extraction preserves original group indexes: group zero is the full match, unmatched groups are null, and invalid group indexes return null. Malformed computed patterns raise execution errors on both backends when the input, pattern, and group are non-null and the group index is nonnegative. A validation error includes the function or operator name and a byte range within the decoded pattern, inside the existing signature-binding diagnostic.

regexp_count(string, pattern)

Counts the number of times a regular expression pattern matches in a string.

Parameters

• string - String expression to search within
• pattern - String expression representing the regular expression pattern

Description

The regexp_count() function returns the number of non-overlapping matches of the specified regular expression pattern within the input string. If no matches are found, it returns 0. The pattern uses standard regular expression syntax.

regexp_extract_all(string, pattern) / regexp_extract_all(string, pattern, group)

Extracts all matches of a regular expression pattern from a string.

Parameters

• string - String expression to search within
• pattern - String expression representing the regular expression pattern
• group (optional) - Integer specifying which capture group to extract

Description

The regexp_extract_all() function returns an array containing all matches of the specified pattern. When used with two parameters, it returns the entire match. When used with three parameters, it returns the specified capture group from each match.

If no matches are found, it returns an empty array. Capture groups are numbered starting from 1, with 0 representing the entire match.

regexp_extract(string, pattern) / regexp_extract(string, pattern, group)

Extracts the first match of a regular expression pattern from a string.

Parameters

• string - String expression to search within
• pattern - String expression representing the regular expression pattern
• group (optional) - Integer specifying which capture group to extract

Description

The regexp_extract() function returns the first match of the specified pattern. When used with two parameters, it returns the entire match. When used with three parameters, it returns the specified capture group from the first match.

If no match is found, it returns null. Capture groups are numbered starting from 1, with 0 representing the entire match.

regexp_like(string, pattern)

Tests whether a string matches a regular expression pattern.

Parameters

• string - String expression to test
• pattern - String expression representing the regular expression pattern

Description

The regexp_like() function returns true if the input string contains a match for the specified regular expression pattern, false otherwise. This function tests for the presence of a match anywhere within the string, not just at the beginning or end.

regexp_position(string, pattern) / regexp_position(string, pattern, start) / regexp_position(string, pattern, start, occurrence)

Returns the position of a regular expression match within a string.

Parameters

• string - String expression to search within
• pattern - String expression representing the regular expression pattern
• start (optional) - Integer specifying the starting position for the search
• occurrence (optional) - Integer specifying which occurrence to find

Description

The regexp_position() function returns the 1-based position of the first character of a pattern match within the string. When used with the start parameter, it begins searching from that position. When used with the occurrence parameter, it finds the nth occurrence of the pattern.

If no match is found, it returns 0. The start position is 1-based, and the occurrence count begins at 1 for the first match.

regexp_replace(string, pattern) / regexp_replace(string, pattern, replacement)

Replaces matches of a regular expression pattern in a string.

Parameters

• string - String expression to search within
• pattern - String expression representing the regular expression pattern
• replacement (optional) - String expression to replace matches with

Description

The regexp_replace() function replaces all matches of the specified pattern. When used with two parameters, it removes all matches (replaces with empty string). When used with three parameters, it replaces matches with the specified replacement string.

If no matches are found, the original string is returned unchanged.

Replacement strings

Hamelin passes replacement strings to the backend without normalization. ${name} is supported by both DataFusion and Trino for named captures, for example:

SET result = regexp_replace('a', r'(?<letter>a)', '${letter}x')

This returns ax on both backends. Other replacement forms differ:

Replacement	DataFusion	Trino
$name	Named capture	Not supported
${1}x	Capture 1 followed by x	Not supported
$1x	Looks up a capture named 1x	Capture 1 followed by x
$$	Literal dollar sign	Not supported
r'\$'	Backslash followed by a dollar sign	Literal dollar sign

For backend-specific behavior, consult the DataFusion documentation and Trino documentation.

regexp_split(string, pattern)

Splits a string using a regular expression pattern as the delimiter.

Parameters

• string - String expression to split
• pattern - String expression representing the regular expression pattern to use as delimiter

Description

The regexp_split() function splits the input string at each occurrence of the specified pattern and returns an array of the resulting substrings. The pattern matches are not included in the result array.

If the pattern is not found, the function returns an array containing the original string as a single element. If the pattern matches at the beginning or end of the string, empty strings may be included in the result array.