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.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