REGEXP_COUNT

REGEXP_COUNT(x, pattern [, start [, match_option]]) searches in x for the pattern and returns the number of times the pattern is found in x.

  • start position to begin the search. The default is 1, which is the first character in x.
  • match_option to change the default matching.

The metacharacters list:

MetacharactersMeaning
\Specify the escape sequence
\dDigit character
\DNon-digit character
\wWord character
\WNon-word character
\sWhitespace character
\SNon-whitespace character
\AMatches only at the beginning of a string or before a newline character at the end of a string
\ZMatches only at the end of a string
^Matches the position at the start of the string.
$Matches the position at the end of the string.
*Matches the preceding character zero or more times.
+Matches the preceding character one or more times.
?Matches the preceding character zero or one time.
*?Matches the preceding pattern element 0 or more times
+?Matches the preceding pattern element 1 or more times
??Matches the preceding pattern element 0 or 1 time
{n}Matches a character exactly n times, where n is an integer.
{n,}Matches the preceding pattern element at least n times
{n,m}Matches a character at least n times and at most m times, where n and m are both integers.
.Matches any single character except null.
(pattern)A subexpression that matches the specified pattern.
x|yMatches x or y, where x and y are one or more characters. war|peace matches war or peace.
[abc]Matches any of the enclosed characters.
[a-z]Matches any character in the specified range.
[:alphanum:]matches alphanumeric characters 0-9, A-Z, and a-z.
[:alpha:]matches alphabetic characters A-Z and a-z.
[:blank:]matches space or tab.
[:digit:]matches digits 0-9.
[:graph:]matches non-blank characters.
[:lower:]matches lowercase alphabetic characters a-z.
[:print:]is similar to [:graph:] except [:print:] includes the space character.
[:punct:]matches punctuation characters .,"`, and so on.
[:space:]matches all whitespace characters.
[:upper:]matches all uppercase alphabetic characters A-Z.
[:xdigit:]matches characters permissible in a hexadecimal number 0-9, A-F, and a-f.
[..]Matches one collation element, like a multicharacter element.
[==]Specifies equivalence classes.
\nA backreference to an earlier capture, where n is a positive integer.

Example:

ExampleDescription
\nmatches the newline character
\\matches \
\(matches (
^Amatches if A is the first character in the string.
$Bmatches if B is the last character in the string.
f*dmatches flood, food, and so on.
fo+dmatches fod, food, and so on.
fo?dmatches fd and fod only.
fo{2}dmatches food.
fo{2,3}dmatches food and foood only.
[ab]bcmatches abc and bbc.
[a-c]bcmatches abc, bbc, and cbc.

SELECT REGEXP_COUNT('asdf asdf ssss sss ss','s[[:alpha:]]{2}') AS result FROM dual;
Home »
Oracle »
String Functions » 

Related: