"scott.marlowe" <scott.marlowe@ihs.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
givenstring according to a given numeric starting position and a given numeric length. Truncation occurs
whenthe 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