regexp_instr function (Databricks SQL)

Returns the position of the first substring in str that matches regexp.

Requires: SQL warehouse version 2022.35 or higher. This version is available in the Preview channel.

Syntax

regexp_instr( str, regexp )

Arguments

  • str: A STRING expression to be matched.

  • regexp: A STRING expression with a pattern.

Returns

A STRING.

The regexp string must be a Java regular expression. String literals are unescaped. For example, to match '\abc', a regular expression for regexp can be '^\\abc$'. In case of a malformed regexp the function returns an INVALID_PARAMETER_VALUE error. If either argument is NULL or the pattern is not found the result is NULL.

Examples

> SELECT regexp_instr('Steven Jones and Stephen Smith are the best players', 'Ste(v|ph)en');
 1

> SELECT regexp_instr('Mary had a little lamb', 'Ste(v|ph)en');
 0

> SELECT regexp_instr(NULL, 'Ste(v|ph)en');
 NULL

> SELECT regexp_instr('Mary had a little lamb', NULL);
 NULL