REGEXP_COUNT returns the number of times a regular expression matches in a string. It answers questions that would otherwise need a loop or a trick with LENGTH and REPLACE: how many words a review has, how many times it mentions a term, how many digits a phone number contains.
Code for This Guide
The main example is in the examples/character-functions folder of the Oracle Database 26ai code repository on GitHub, with its output. It queries NIMBUS, the sample schema of a fictional airline, which you can install with the scripts in the setup/nimbus folder.
It comes from Oracle Database 26ai SQL and PL/SQL Book.
Syntax
Syntax:
regexp_count(source_char, pattern [, position [, match_param]])
| Argument | Meaning |
|---|---|
| source_char | The string to search |
| pattern | The regular expression to count |
| position | The character to start counting from; default 1 |
| match_param | Flags such as i to ignore case |
Matches do not overlap: after one match, counting continues after its end.
Count Words, Terms, and Sentences
This query counts, for three reviews, the words (runs of word characters), the occurrences of "the" in any case, and the sentence-ending punctuation marks.
Example:
select review_id,
regexp_count(review_text, '\w+') as words,
regexp_count(review_text, 'the', 1, 'i') as the_count,
regexp_count(review_text, '[.!?]') as sentences
from reviews
where review_id <= 3;Output:
REVIEW_ID WORDS THE_COUNT SENTENCES
____________ ________ ____________ ____________
1 22 2 2
2 20 2 2
3 15 2 2'\w+' matches a whole word at a time, so each word counts once. 'the' with the i flag also counts The at the start of a sentence, and it would count the letters inside other, too; add word boundaries with spaces or punctuation in the pattern when that matters.
Count Characters and Groups
The difference between '\d' and '\d+' is the difference between counting digits and counting groups of digits.
Example:
select phone,
regexp_count(phone, '\d') as digits,
regexp_count(phone, ' ') as spaces,
regexp_count(phone, '\d+') as digit_groups
from customers
where customer_id in (1, 2, 3);Output:
PHONE DIGITS SPACES DIGIT_GROUPS ___________________ _________ _________ _______________ +971 22 414 1147 12 3 4 +91 82 899 2343 11 3 4 +44 20 559 4746 11 3 4
Each number has 11 or 12 digits in four groups separated by three spaces. A check such as regexp_count(phone, '\d') between 8 and 15 is a quick sanity test for phone numbers.
Things to Know
- REGEXP_COUNT returns 0 when there is no match and NULL when the source is NULL.
- For a fixed substring, REGEXP_COUNT is simpler than the older (length(s) - length(replace(s, x))) / length(x) technique.
- Use it in WHERE to find rows with too many or too few occurrences, such as names with more than one space.
Related Guides
Conclusion
REGEXP_COUNT counts the non-overlapping matches of a regular expression in a string. Count single characters with a class such as \d, whole runs with \d+ or \w+, ignore case with the i flag, and start later in the string with position.
