Обсуждение: 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
returnfrom the start *  of the string adjusting the length to be consistent with the *  "negative start" per SQL92. If
thelength 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.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