Re: substr negative indexes

Guy Rouillier <guyr-ml1(at)burntmail(dot)com> writes:
> Does the spec leave implementation of indexes on the substr() function
> less than one undefined?

SQL99 defines the result of

<character substring function> ::=
SUBSTRING <left paren> <character value expression> FROM <start position>
[ FOR <string length> ] <right paren>

as

a) Let C be the value of the <character value expression>, let
LC be the length of C, and let S be the value of the <start
position>.

b) If <string length> is specified, then let L be the value of
<string length> and let E be S+L. Otherwise, let E be the
larger of LC + 1 and S.

c) If either C, S, or L is the null value, then the result of
the <character substring function> is the null value.

d) If E is less than S, then an exception condition is raised:
data exception - substring error.

e) Case:

i) If S is greater than LC or if E is less than 1 (one), then
the result of the <character substring function> is a zero-
length string.

ii) Otherwise,

1) Let S1 be the larger of S and 1 (one). Let E1 be the
smaller of E and LC+1. Let L1 be E1-S1.

2) The result of the <character substring function> is
a character string containing the L1 characters of C
starting at character number S1 in the same order that
the characters appear in C.

and unless I'm mistaken, our behavior conforms to the spec and Oracle's
doesn't.

Strictly speaking, the spec doesn't define the behavior of "SUBSTR" at
all, only "SUBSTRING" with this weird FROM/FOR argument syntax. But
PG treats SUBSTR(x,y,z), SUBSTRING(x,y,z) and SUBSTRING(x FROM y FOR z)
all the same. Possibly Oracle conforms to spec for SUBSTRING but
their SUBSTR acts differently?

regards, tom lane