Re: bug in substring???

I'm using substring. Since I'm a coder more than a database guy, I
expected this:

select substring('abcdefgh',0,4);

would give me

abcd

but it gives me a left aligned 'abc'

select substring('abcdefgh',1,4);

works fine.

select substring('abcdefgh',-4,4);

gives me nothing. Shouldn't a negative offset, or even 0 offset result in
an error or something here? Or is there a special meaning to a negative
offset I'm not getting?

Just wondering.

scott.marlowe wrote:
> gives me nothing. Shouldn't a negative offset, or even 0 offset result in
> an error or something here? Or is there a special meaning to a negative
> offset I'm not getting?

In varlena.c there is this comment:

* text_substr()
* Return a substring starting at the specified position.
* - thomas 1997-12-31
*
* Input:
* - string
* - starting position (is one-based)
* - string length
*
* If the starting position is zero or less, then return from the start
* of the string adjusting the length to be consistent with the
* "negative start" per SQL92. If the length is less than zero, return
* the remaining string.

Joe

On Fri, 6 Feb 2004, Joe Conway wrote:

> scott.marlowe wrote:
> > gives me nothing. Shouldn't a negative offset, or even 0 offset result in
> > an error or something here? Or is there a special meaning to a negative
> > offset I'm not getting?
>
> In varlena.c there is this comment:
>
> * text_substr()
> * Return a substring starting at the specified position.
> * - thomas 1997-12-31
> *
> * Input:
> * - string
> * - starting position (is one-based)
> * - string length
> *
> * If the starting position is zero or less, then return from the start
> * of the string adjusting the length to be consistent with the
> * "negative start" per SQL92. If the length is less than zero, return
> * the remaining string.

thanks. I just got done looking up the SQL explanation, and I think my
head exploded. Thanks for the heads up.

"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