Message-ID: <7df7835d-a587-4d8a-a415-72e66cde5aee@gmail.com>
Date: Fri, 18 Sep 2026 09:57:05 +0200
MIME-Version: 1.0
User-Agent: Mozilla Thunderbird
Subject: Re: different result of regexp_instr than on Oracle
To: pgsql-hackers@lists.postgresql.org
References: 
 <CAFj8pRAQu6cwOEqFFH7e2NCw_KupryvEn7eAJ3hGQ+EjfFzivA@mail.gmail.com>
Content-Language: en-US
From: Gilles Darold <gillesdarold@gmail.com>
In-Reply-To: 
 <CAFj8pRAQu6cwOEqFFH7e2NCw_KupryvEn7eAJ3hGQ+EjfFzivA@mail.gmail.com>
Content-Type: text/plain; charset=UTF-8; format=flowed
Content-Transfer-Encoding: 8bit
Archived-At: 
 <https://www.postgresql.org/message-id/7df7835d-a587-4d8a-a415-72e66cde5aee%40gmail.com>
Precedence: bulk

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