Hello Ann,

Thursday, October 14, 2004, 6:17:27 PM, you wrote:

> Does anyone know the rules for the starting position
> of an 'ordinary' substring function in standard SQL?

> I have found that the first character is 1 and that
> the starting position must be a numeric, scale 0.
> Some implementations allow 0 and negative starting
> positions. For example:

> substring ('abcdefghij' from 1 for 5) => 'abcde'
> substring ('abcdefghij' from 0 for 5) => 'abcd'
> substring ('abcdefghij' from -1 for 5) => 'abc'

> Is this right? wrong? implementation defined?

SQL Standard! The SQL Standard does seem to allow negative starting
values, probably follow the numeric value expression permitted syntax.
Because, the standard does mention that if the ending position is
lower than 1 then the resulting value is an empty string.
i.e.: substring ('abcdefghij' from -5 for 5) => ''

Image if you would be using variables, expressions or field values in
SUBSTRING, the resulting value could be a negative value. But, the
standard do mention that the count value has to be positive, because
the ending position has to be greater than the starting position, or
an exception has to be thrown.

--
Best regards,
Daniel Rail
Senior Software Developer
ACCRA Group Inc. (www.accra.ca)
ACCRA Med Software Inc. (www.filopto.com)