pg.ddx.io pgsql-hackers@postgresql.org mailing list archive
help / color / mirror / Atom feeddifferent result of regexp_instr than on Oracle
3+ messages / 2 participants
[nested] [flat]
* different result of regexp_instr than on Oracle
@ 2026-09-18 05:21 Pavel Stehule <pavel.stehule@gmail.com>
2026-09-18 07:57 ` Re: different result of regexp_instr than on Oracle Gilles Darold <gillesdarold@gmail.com>
0 siblings, 1 reply; 3+ messages in thread
From: Pavel Stehule @ 2026-09-18 05:21 UTC (permalink / raw)
To: PostgreSQL Hackers <pgsql-hackers@lists.postgresql.org>
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?
Regards
Pavel
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: different result of regexp_instr than on Oracle
2026-09-18 05:21 different result of regexp_instr than on Oracle Pavel Stehule <pavel.stehule@gmail.com>
@ 2026-09-18 07:57 ` Gilles Darold <gillesdarold@gmail.com>
2026-09-18 09:06 ` Re: different result of regexp_instr than on Oracle Pavel Stehule <pavel.stehule@gmail.com>
0 siblings, 1 reply; 3+ messages in thread
From: Gilles Darold @ 2026-09-18 07:57 UTC (permalink / raw)
To: pgsql-hackers@lists.postgresql.org
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
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: different result of regexp_instr than on Oracle
2026-09-18 05:21 different result of regexp_instr than on Oracle Pavel Stehule <pavel.stehule@gmail.com>
2026-09-18 07:57 ` Re: different result of regexp_instr than on Oracle Gilles Darold <gillesdarold@gmail.com>
@ 2026-09-18 09:06 ` Pavel Stehule <pavel.stehule@gmail.com>
0 siblings, 0 replies; 3+ messages in thread
From: Pavel Stehule @ 2026-09-18 09:06 UTC (permalink / raw)
To: Gilles Darold <gillesdarold@gmail.com>; +Cc: pgsql-hackers@lists.postgresql.org
pá 18. 9. 2026 v 9:57 odesílatel Gilles Darold <gillesdarold@gmail.com>
napsal:
> 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
>
>
>
ok
thank you for explanation
Regards
Pavel
^ permalink raw reply [nested|flat] 3+ messages in thread
end of thread, other threads:[~2026-09-18 09:06 UTC | newest]
Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2026-09-18 05:21 different result of regexp_instr than on Oracle Pavel Stehule <pavel.stehule@gmail.com>
2026-09-18 07:57 ` Gilles Darold <gillesdarold@gmail.com>
2026-09-18 09:06 ` Pavel Stehule <pavel.stehule@gmail.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox