PostgreSQL 18 reference · String Functions
PostgreSQL REGEXP_COUNT() Function
Count how many times a regular expression matches in a string. Added in PostgreSQL 15.
PostgreSQL 18 Since 15
Syntax
regexp_count(string, pattern, start, flags)
Parameters
| Parameter | Description |
|---|---|
string |
The input text. |
pattern |
A POSIX regular expression. |
start optional |
The character to start from. Default: 1 |
flags optional |
For example i. |
Return value
An integer.
Examples
SELECT regexp_count('banana', 'an') AS an, regexp_count('Hello World', '[aeiou]', 1, 'i') AS vowels;
Output
an | vowels ----+-------- 2 | 3 (1 row)
Run this on your computer — see “Get Started” for setup.
SELECT name, regexp_count(name, '\s') + 1 AS words
FROM products
LIMIT 3;
Output
name | words --------------------------+------- SQL for Beginners | 3 Clean Code | 2 The Pragmatic Programmer | 3 (3 rows)
Run this on your computer — see “Get Started” for setup.
Version history
| Version | Change |
|---|---|
| PostgreSQL 15 | Added |
Related
REGEXP_MATCH()· Return the captured groups of the first regular-expression match as a text array, or NULL if there's no match.LENGTH()· Return the number of characters in a string. char_length() is the standard name; octet_length() counts bytes.- SQL String Functions · tutorial