w3resource

Oracle REGEXP_COUNT function

Description

The Oracle REGEXP_COUNT function is used to count the number of times that a pattern occurs in a string. It returns an integer indicating the number of occurrences of a pattern. If no match is found, then the function returns 0.

Syntax:

REGEXP_COUNT (source_char, pattern [, position [, match_param]])

Parameters:

Name Description Data Types
source_char The string to search. CHAR, VARCHAR2, NCHAR, NVARCHAR2, CLOB, or NCLOB.
pattern pattern is the regular expression. CHAR, VARCHAR2, NCHAR, or NVARCHAR2.
position Optional. The position is a positive integer indicating the character of source_char where the search will start. If omitted, it defaults to 1 which is the first position in the string.  
match_param Optional. It lets you allow to modify the matching behavior for the REGEXP_COUNT function.  

Here is the list of pattern formatting operator:

Operator Description
\d A digit character.
\D A nondigit character.
\w A word character.
\W A nonword character.
\s A whitespace character.
\S A non-whitespace character.
\A Matches only at the beginning of a string, or before a newline character at the end of a string.
\Z Matches only at the end of a string.
*? Matches the preceding pattern element 0 or more times (nongreedy).
+? Matches the preceding pattern element 1 or more times (nongreedy).
?? Matches the preceding pattern element 0 or 1 time (nongreedy).
{n}? Matches the preceding pattern element exactly n times (nongreedy).
{n,}? Matches the preceding pattern element at least n times (nongreedy).
{n,m}? Matches the preceding pattern element at least n but not more than mtimes (nongreedy).

Here is the list of Regular Expression Operators and Metasymbols:

Operator Description
\ The backslash character can have four different meanings depending on the context. It can: Stand for itself, Quote the next character, Introduce an operator, Do nothing
* Matches zero or more occurrences
+ Matches one or more occurrences
? Matches zero or one occurrence
| Alternation operator for specifying alternative matches
^ Matches the beginning of a string by default. In multiline mode, it matches the beginning of any line anywhere within the source string.
$ Matches the end of a string by default. In multiline mode, it matches the end of any line anywhere within the source string.
. Matches any character in the supported character set except NULL
[ ] Bracket expression for specifying a matching list that should match any one of the expressions represented in the list. A non-matching list expression begins with a circumflex (^) and specifies a list that matches any character except for the expressions represented in the list. To specify a right bracket (]) in the bracket expression, place it first on the list (after the initial circumflex (^), if any). To specify a hyphen in the bracket expression, place it first on the list (after the initial circumflex (^), if any), last in the list, or as an ending range point in a range expression.
( ) Grouping expression, treated as a single subexpression
{m} Matches exactly m times
{m,} Matches at least m times
{m,n} Matches at least m times but no more than n times
\n The backreference expression (n is a digit between 1 and 9) matches the nth subexpression enclosed between '(' and ')' preceding the \n
[..] Specifies one collation element, and can be a multicharacter element (for example, [.ch.] in Spanish)
[: :] Specifies character classes (for example, [:alpha:]). It matches any character within the character class.
[==] Specifies equivalence classes. For example, [=a=] matches all characters having the base letter 'a'.

Here is the list of values for match_param:

Value Description
'c' Perform case-sensitive matching.
'i' Perform case-insensitive matching.
'n' Allows the period character (.) to match the newline character. By default, the period is a wildcard.
'm' expression is assumed to have multiple lines, where ^ is the start of a line and $ is the end of a line, regardless of the position of those characters in expression. By default, an  expression is assumed to be a single line.
'x' Whitespace characters are ignored. By default, whitespace characters are matched like any other character.

Applies to

Oracle 12c, Oracle 11g

Examples: Oracle REGEXP_COUNT function

The following example counts the number of times the character 't' appears in a string.

SELECT REGEXP_COUNT ('The web development Tutorial', 't') FROM dual;

Sample Output:

REGEXP_COUNT('THEWEBDEVELOPMENTTUTORIAL','T')
---------------------------------------------
                                            2

The above example will return 2 because it is counting the number of occurrences of 't' in the string. Here we did not specify a match_parameter value, the REGEXP_COUNT function will perform a case-sensitive search which means that the 'T' characters will not be included in the count.

In the following example, we have provided a start_position of 1 and a match_parameter of 'i', the query will return 4 as the result. This time, both 't' and 'T' values would be included in the count.

SELECT REGEXP_COUNT ('The web development Tutorial', 't',1,'i') FROM dual;

Sample Output:

REGEXP_COUNT('THEWEBDEVELOPMENTTUTORIAL','T',1,'I')
---------------------------------------------------
                                                  4

The following example will return the number of times that the word 'The' appears in the string.

SELECT REGEXP_COUNT ('Match the string for multiple character', 'The',1,'i') FROM dual;

Sample Output:

REGEXP_COUNT('MATCHTHESTRINGFORMULTIPLECHARACTER','THE',1,'I')
--------------------------------------------------------------
                                                             1

Previous: LENGTH
Next: REGEXP_INSTR



Share this Tutorial / Exercise on : Facebook and Twitter