From: Bruce Momjian <bruce@momjian.us>
To: dansonlinepresence@gmail.com
Cc: pgsql-docs@lists.postgresql.org
Subject: Re: substring start position behavior
Date: Tue, 5 Mar 2024 22:24:47 -0500
Message-ID: <Zefh_2-e9ILgmCjW@momjian.us> (raw)
In-Reply-To: <CAK5Hk6=hUM=M_rRQThjZNBVhc=KuHoCs=+n0OjLeDdspChFo9A@mail.gmail.com>
References: <170905442373.643.11536838320909376197@wrigleys.postgresql.org>
<Zee2K8FeRyT4NY5O@momjian.us>
<CAK5Hk6=hUM=M_rRQThjZNBVhc=KuHoCs=+n0OjLeDdspChFo9A@mail.gmail.com>
On Tue, Mar 5, 2024 at 09:47:54PM -0500, dansonlinepresence@gmail.com wrote:
> Thanks for all that context and diff! The odd behavior is indeed in
> the SQL spec. At least I was convinced of that on postgres IRC by
> someone who seemed to have quoted from the spec. I don't think the
> feature ought to be hidden because it's odd.
>
> If I may suggest some specificity to the wording here, I think "start
> values less than 1" would avoid confusion about whether 0 is
> nonpositive or not, and bring attention to the function being
> 1-indexed rather than 0-indexed.
Sure, updated patch attached.
--
Bruce Momjian <bruce@momjian.us> https://momjian.us
EDB https://enterprisedb.com
Only you can decide what is important to you.
Attachments:
[text/x-diff] substring.diff (1.2K, ../Zefh_2-e9ILgmCjW@momjian.us/2-substring.diff)
download | inline diff:diff --git a/doc/src/sgml/func.sgml b/doc/src/sgml/func.sgmlindex e5fa82c161..f981793f00 100644--- a/doc/src/sgml/func.sgml+++ b/doc/src/sgml/func.sgml@@ -2783,6 +2783,13 @@ SELECT NOT(ROW(table.*) IS NOT NULL) FROM TABLE; -- detect at least one null in
specified. Provide at least one of <parameter>start</parameter>
and <parameter>count</parameter>.
</para>
+ <para>+ <parameter>start</parameter> values less then one specify+ a start position before the first character, and therefore+ <parameter>count</parameter> must be two greater than the absolute+ value of <parameter>start</parameter> to begin returning characters+ from the start of the string.+ </para>
<para>
<literal>substring('Thomas' from 2 for 3)</literal>
<returnvalue>hom</returnvalue>
@@ -2794,6 +2801,10 @@ SELECT NOT(ROW(table.*) IS NOT NULL) FROM TABLE; -- detect at least one null in
<para>
<literal>substring('Thomas' for 2)</literal>
<returnvalue>Th</returnvalue>
+ </para>+ <para>+ <literal>substring('Thomas' from -4 for 8)</literal>+ <returnvalue>Tho</returnvalue>
</para></entry>
</row>
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-docs@postgresql.org
Cc: bruce@momjian.us, dansonlinepresence@gmail.com, pgsql-docs@lists.postgresql.org
Subject: Re: substring start position behavior
In-Reply-To: <Zefh_2-e9ILgmCjW@momjian.us>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
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