regexp_count function

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

Returns the number of times str matches the regexp pattern.

Syntax

regexp_count( 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.

Common error conditions

Examples

Count occurrences of a pattern

The pattern Ste(v|ph)en matches both Steven and Stephen.

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

Count the numbers in a string

> SELECT regexp_count('There are 10 cats, 20 dogs, and 30 birds', r'\d+');
 3

Count the words in a string

> SELECT regexp_count('one two three four', r'\w+');
 4

Count case-insensitively

Use the (?i) inline flag to ignore case.

> SELECT regexp_count('Yes yes YES', r'(?i)yes');
 3

No match returns 0

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

NULL input returns NULL

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

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

Invalid regex pattern

> SELECT regexp_count('abc', '[invalid');
  Error: INVALID_PARAMETER_VALUE.PATTERN
  • Last updated on