Pular para o conteúdo principal
Última atualização em

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.

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

Count the numbers in a string​

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

Count the words in a string​

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

Count case-insensitively​

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

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

No match returns 0​

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

NULL input returns NULL​

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

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

Invalid regex pattern​

SQL
> SELECT regexp_count('abc', '[invalid');
  Error: INVALID_PARAMETER_VALUE.PATTERN
Nesta página