Re: substring start position behavior

On Tue, Feb 27, 2024 at 05:20:23PM +0000, PG Doc comments form wrote:
> The following documentation comment has been logged on the website:
> 
> Page: https://www.postgresql.org/docs/16/functions-string.html
> Description:
> 
> Hey,
> 
> I was confused by substring behavior today, when giving 0 as a start
> position. I understand now that string indices are 1-based, have a certain
> flexibility about where to start (allowing negative start positions), and
> that this is defined in the standard SQL spec.
> 
> I'm comfy with all this, but I think it'd be nice to have a hint in the pg
> substring docs for nonpositive start positions, so that users don't have to
> have paid for the standard SQL spec to get past this. To me, substring seems
> like a relatively common function with relatively surprising behavior.

I dug into this and quickly became as confused as you were.  The best
explanation I found of the current behavior is here (with diagram):

    https://www.mssqltips.com/sqlservertutorial/9374/sql-substring-function/
    SELECT SUBSTRING('Hello world',-2,5) as msg

The last Postgres community discussion of this behavior I could find was
from 2007:

    https://www.postgresql.org/message-id/flat/12803.1168804636%40sss.pgh.pa.us#8316fb2298c9e49f77867a1ae2ead447

This web page explains the feature:

    https://stackoverflow.com/questions/33462061/sql-server-substring-position-negative-value

but also asks:

    now the only question that remains is, "why would anyone need it
    to behave this way?"

and the answer given is:

    @mao47 Well, it depends. I am not an author of implementation of
    SUBSTR but I guess with negative index it behaves like LEFT(string,
    LEN(string) - 1 - index). It works the same way in PostgreSQL so maybe
    it is SQL standard.

Informix has substring() which matches the SQL standard, and substr()
which uses negative start from the end of the string:

    https://www.ibm.com/docs/en/informix-servers/14.10?topic=smf-substr-function

Oracle doesn't have substring(), just substr(), and matches Informix
behavior, I think.

I have developed the attached doc patch to document this.  The only
question is whether this substring behavior is so odd that we should not
document it.

-- 
  Bruce Momjian  <bruce@momjian.us>        https://momjian.us
  EDB                                      https://enterprisedb.com

  Only you can decide what is important to you.