Categories:

String Functions (Regular Expressions)

REGEXP_COUNT

Returns the number of times that a pattern occurs in a string.

See also String Functions (Regular Expressions).

Syntax

REGEXP_COUNT( <subject> , <pattern> [ , <position> , <parameters> ] )
Copy

Arguments

Required:

subject

Subject to match.

pattern

Pattern to match.

Optional:

position

Number of characters from the beginning of the string where the function starts searching for matches.

Default: 1 (the search for a match starts at the first character on the left)

parameters

String of one or more characters that specifies the parameters used for searching for matches. Supported values:

c , i , m , e , s

For more details, see Specifying the Parameters for the Regular Expression.

Default: c

Usage Notes

Collation Details

Arguments with collation specifications are currently not supported.

Examples

The following example counts occurrences of the word was. Matching begins at the 1st character in the string:

select regexp_count('It was the best of times, it was the worst of times', '\\bwas\\b', 1) as "result" from dual;

+--------+
| result |
|--------|
|      2 |
+--------+
Copy

The following example illustrates overlapping occurrences:

create or replace table overlap (id number, a string);
insert into overlap values (1,',abc,def,ghi,jkl,');
insert into overlap values (2,',abc,,def,,ghi,,jkl,');

select * from overlap;

select id, regexp_count(a,'[[:punct:]][[:alnum:]]+[[:punct:]]', 1, 'i') from overlap;

+----+--------------------------------------------------------------+
| ID | REGEXP_COUNT(A,'[[:PUNCT:]][[:ALNUM:]]+[[:PUNCT:]]', 1, 'I') |
|----+--------------------------------------------------------------|
|  1 |                                                            2 |
|  2 |                                                            4 |
+----+--------------------------------------------------------------+
Copy