Re: bug in substring???

"scott.marlowe" <scott(dot)marlowe(at)ihs(dot)com> writes:
> thanks. I just got done looking up the SQL explanation, and I think my
> head exploded. Thanks for the heads up.

The formal definition seems unnecessarily complicated :-(, but the spec
authors' intent is reasonably clear from this paragraph in the
"Concepts" section of SQL92:

<character substring function> is a triadic function, SUBSTRING,
that returns a string extracted from a given string according
to a given numeric starting position and a given numeric length.
Truncation occurs when the implied starting and ending positions
are not both within the given string.

In other words, they consider that a zero or negative start position
should be truncated back to the actual start position (1) in much the
same way that a too-large length specification would be truncated to
match the actual end position.

AFAICT the only case in which SUBSTRING is supposed to raise an error is
when you specify a negative length.

regards, tom lane