Обсуждение: 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