regexp_count function

Applies to: Databricks SQL 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 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, the result is NULL.

Examples

SQL

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

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

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

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

Related functions

• ilike operator
• like operator
• regexp operator
• regexp_extract_all function
• regexp_instr function
• regexp_replace function
• regexp_substr function
• rlike operator
• split function