Re: Can Postgres beat Oracle for regexp_count? - Mailing list pgsql-general

On Wed, Feb 2, 2022 at 4:26 PM Tom Lane <tgl@sss.pgh.pa.us> wrote:
>
> "David G. Johnston" <david.g.johnston@gmail.com> writes:
> > Given we don't have a regexp_count function this isn't surprising...
>
> FYI, it's there in HEAD.
>
> In the meantime, you could possibly do something like
>
> =# select count(*) from regexp_matches('My High Street', '([A-Z][a-z]+[\s])', 'g');
>  count
> -------
>      2
> (1 row)

alternate version:
postgres=# select array_upper(regexp_split_to_array('My High Street My
High Street', 'My High Street'), 1) - 1;
 ?column?
──────────
        2

can help to slide this into complex queries a little bit easier by
avoiding the aggregation :-).

merlin