- Categories:
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> ] )
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¶
For usage notes, see the General Usage Notes for regular expression functions.
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 | +----+--------------------------------------------------------------+