Re: different result of regexp_instr than on Oracle - Mailing list pgsql-hackers

Le 18/09/2026 à 07:21, Pavel Stehule a écrit :
> Hi
>
> I got an error report for regexp_instr in Orafce.
>
> SELECT REGEXP_INSTR('ABC', 'A', 1, 1, 0, '', 1) AS SUBEXPR_1
>
> Oracle returns 0, Postgres 1.
>
> Because Orafce now share this functionality with Postgres, there
> should be same problem
>
> Is this behaviour expected?

Hi Pavel,

That's a good catch. I have tested orafce with your commit 546ee72 it
now works as expected for Oracle compatibility. About PostgreSQL
regexp_instr() behavior, I think it is expected that it returns 1, per
the documentation:

"the subexpr is an integer indicating which subexpression is of
interest: the result identifies the position of the substring matching
that subexpression. Subexpressions are numbered in the order of their
leading parentheses. When subexpr is omitted or zero, the result
identifies the position of the whole match regardless of parenthesized
subexpressions".

When no capture group are defined in the pattern, there's an implicit
capture group for the whole match when subexpr is 0 or 1.

contrib_regression=# SELECT REGEXP_INSTR('ABC', 'B', 1, 1, 0, '', 0) AS
SUBEXPR_0;
  subexpr_0
-----------
          2

contrib_regression=# SELECT REGEXP_INSTR('ABC', 'B', 1, 1, 0, '', 1) AS
SUBEXPR_1;
  subexpr_1
-----------
          2

contrib_regression=# SELECT REGEXP_INSTR('ABC', 'B', 1, 1, 0, '', 2) AS
SUBEXPR_2;
  subexpr_2
-----------
          0


contrib_regression=# SELECT REGEXP_INSTR('ABC', 'B(C)', 1, 1, 0, '', 0)
AS SUBEXPR_0;
  subexpr_0
-----------
          2
(1 ligne)

contrib_regression=# SELECT REGEXP_INSTR('ABC', 'B(C)', 1, 1, 0, '', 1)
AS SUBEXPR_1;
  subexpr_1
-----------
          3

Best regards,
--
Gilles