Skip to main content
Last updated on

regexp_instr function

Applies to: check marked yes Databricks SQL check marked yes Databricks Runtime 11.3 LTS and above

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

Syntax​

regexp_instr( str, regexp )

Arguments​

  • str: A STRING expression to be matched.
  • regexp: A STRING expression with a pattern.

Returns​

An INTEGER.

The regexp string must be a regular expression. See Regular expressions for the supported syntax.

When using literals, use raw-literal (r prefix) to avoid escape character pre-processing.

If either argument is NULL, the result is NULL. If the pattern is not found, the result is 0.

Common error conditions​

Examples​

Find the position of the first match​

The pattern Ste(v|ph)en matches Steven at the start of the string, so the position is 1.

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

Find the position of the first digit​

Positions are 1-based.

SQL
> SELECT regexp_instr('Order 1234 shipped', r'\d');
 7

Find the position of a character​

SQL
> SELECT regexp_instr('alice@example.com', r'@');
 6

No match returns 0​

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

NULL input returns NULL​

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

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

Invalid regex pattern​

SQL
> SELECT regexp_instr('abc', '[invalid');
  Error: INVALID_PARAMETER_VALUE.PATTERN
On this page