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.  




Fri, 6 Feb 2004 14:21:43 -0700 (MST)
"scott.marlowe" <scott.marlowe@ihs.com>
bug in substring???
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



Fri, 06 Feb 2004 15:09:57 -0800
Joe Conway <mail@joeconway.com>
Re: bug in substring???
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.


Fri, 6 Feb 2004 16:48:13 -0700 (MST)
"scott.marlowe" <scott.marlowe@ihs.com>
Re: bug in substring???
"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 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

Sat, 07 Feb 2004 01:39:13 -0500
Tom Lane <tgl@sss.pgh.pa.us>
Re: bug in substring??? 