On Tue, Mar 5, 2024 at 09:47:54PM -0500, dansonlineprese...@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 <br...@momjian.us> https://momjian.us
EDB https://enterprisedb.com
Only you can decide what is important to you.
diff --git a/doc/src/sgml/func.sgml b/doc/src/sgml/func.sgml
index 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>
