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:
| Metacharacters | Meaning |
|---|---|
| \ | Specify the escape sequence |
| \d | Digit character |
| \D | Non-digit character |
| \w | Word character |
| \W | Non-word character |
| \s | Whitespace character |
| \S | 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 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|y | Matches 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. |
| \n | A backreference to an earlier capture, where n is a positive integer. |
Example:
| Example | Description |
|---|---|
| \n | matches the newline character |
| \\ | matches \ |
| \( | matches ( |
| ^A | matches if A is the first character in the string. |
| $B | matches if B is the last character in the string. |
| f*d | matches flood, food, and so on. |
| fo+d | matches fod, food, and so on. |
| fo?d | matches fd and fod only. |
| fo{2}d | matches food. |
| fo{2,3}d | matches food and foood only. |
| [ab]bc | matches abc and bbc. |
| [a-c]bc | matches abc, bbc, and cbc. |
SELECT REGEXP_COUNT('asdf asdf ssss sss ss','s[[:alpha:]]{2}') AS result FROM dual;