Categories:

String Functions (Regular Expressions)

# REGEXP_COUNT¶

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

## Syntax¶

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


## 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 Parameters.

Default: c

## 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 |
+--------+


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 |
+----+--------------------------------------------------------------+