REGEXP_COUNT
Counts how many times a regular expression matches in a string.
PostgreSQL 17.5
REGEXP_COUNT(string, pattern[, start[, flags]])Parameters
- stringtext
- String to search in
- patterntext
- Regular expression
- startintegeroptional
- Position to start the search from, 1 by default
- flagstextoptional
- Flags, for example i to ignore case
Return value
An integer; 0 if there are no matches
Examples
Numbers and digits in a string
PostgreSQL 17.5
SELECT REGEXP_COUNT('a1b22c333', '[0-9]+') AS numbers,
REGEXP_COUNT('a1b22c333', '[0-9]') AS digits;Vowels in names
PostgreSQL 17.5
SELECT member_name,
REGEXP_COUNT(member_name, '[aeiou]', 1, 'i') AS vowels
FROM FamilyMembers;Details
The function appeared in PostgreSQL 15. Matches do not overlap: in the string 'aaa' the pattern 'aa' is found once.