Re: Can Postgres beat Oracle for regexp_count?

Shaozhong SHI <shishaozhong(at)gmail(dot)com> writes:
> The following has been attempted but no luck.

> select regexp_matches('My High Street', '([A-Z][a-z]+[\s]*)+', 'g')
> It is intended to match 'My High Street, but it turned out only 'Street'
> was matched.

You've got the parentheses in the wrong place, ie inside not outside the
"+" quantifier. Per the fine manual [1], the result is determined by the
last match of quantified capturing parens.

You could avoid using any capturing parens, so that the result is
the whole match:

regression=# select regexp_matches('My High Street', '(?:[A-Z][a-z]+[\s]*)+', 'g');
regexp_matches
--------------------
{"My High Street"}
(1 row)

or you could do

regression=# select regexp_matches('My High Street', '(([A-Z][a-z]+[\s]*)+)', 'g');
regexp_matches
---------------------------
{"My High Street",Street}
(1 row)

but then you have two sets of capturing parens and you get results for
both, so you might prefer

regression=# select regexp_matches('My High Street', '((?:[A-Z][a-z]+[\s]*)+)', 'g');
regexp_matches
--------------------
{"My High Street"}
(1 row)

In any case, there's no substitute for reading the manual.

regards, tom lane

[1] https://www.postgresql.org/docs/current/functions-matching.html#FUNCTIONS-POSIX-REGEXP