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

From Shaozhong SHI
Subject Re: Can Postgres beat Oracle for regexp_count?
Date
Msg-id CA+i5JwZ_=F=auwbagpDiAP+b3XMPf6sKHDFZDRNAOMNrM5WPmA@mail.gmail.com
Whole thread Raw
In response to Re: Can Postgres beat Oracle for regexp_count?  (Tom Lane <tgl@sss.pgh.pa.us>)
Responses Re: Can Postgres beat Oracle for regexp_count?
List pgsql-general
Many thanks, Tom,

select regexp_matches('My High Street', '(?:[A-Z][a-z]+[\s]*)+', 'g');  looks very interesting.

I did read the documentation, but found it is difficult to read.  Particularly, the documentation on the use ?: does not state clear sense.  There is only limited explanation on ?:.

Is it correct to say that this ?: construction of a regex can be applied for checking whether cell values meet specifications?

Regards,

David

On Thu, 3 Feb 2022 at 05:59, Tom Lane <tgl@sss.pgh.pa.us> wrote:
Shaozhong SHI <shishaozhong@gmail.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

pgsql-general by date:

Previous
From: Simon Riggs
Date:
Subject: Re: Undetected Deadlock
Next
From: Luca Ferrari
Date:
Subject: max_connections different between primary and standby: is it possible?