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

The string to search for matches.

pattern

Pattern to match.

For guidelines on specifying patterns, see String functions (regular expressions).

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:

Parameter

Description

c

Case-sensitive matching

i

Case-insensitive matching

m

Multi-line mode

e

Extract submatches

s

POSIX wildcard character . matches \n

Default: c

For more details, see Specifying the parameters for the regular expression.

Returns

Returns a value of type NUMBER. Returns NULL if any argument is NULL.

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 first character in the string:

SELECT REGEXP_COUNT('It was the best of times, it was the worst of times',
                    '\\bwas\\b',
                    1) AS result;
Copy
+--------+
| RESULT |
|--------|
|      2 |
+--------+

The following example illustrates overlapping occurrences. Create a table and insert data:

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;
Copy
+----+----------------------+
| ID | A                    |
|----+----------------------|
|  1 | ,abc,def,ghi,jkl,    |
|  2 | ,abc,,def,,ghi,,jkl, |
+----+----------------------+

Run a query that uses REGEXP_COUNT to count the number of times that the following pattern is found in each row: a punctuation mark followed by digits and letters, followed by a punctuation mark.

SELECT id,
       REGEXP_COUNT(a,
                    '[[:punct:]][[:alnum:]]+[[:punct:]]',
                    1,
                    'i') AS result
  FROM overlap;
Copy
+----+--------+
| ID | RESULT |
|----+--------|
|  1 |      2 |
|  2 |      4 |
+----+--------+
Language: English