Hi Ann,

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

From the SQL-200n document:
----------------------------------------------
6.29 <string value function>

<character substring function> ::=
SUBSTRING <left paren> <character value expression> FROM <start
position>
[ FOR <string length> ] [ USING <char length units> ] <right paren>

<start position> ::= <numeric value expression>
<string length> ::= <numeric value expression>

<snip>

General Rules

3) If <character substring function> is specified, then:
<snip>

b) Let C be the value of the <character value expression>, let LC be the
length in characters of C, and let S be the value of the <start
position>.

c) If <string length> is specified, then let L be the value of <string
length>
and let E be S+L. Otherwise, let E be the larger of LC + 1 and S.

d) If either C, S, or L is the null value, then the result of the
<character substring function> is the null value.

e) If E is less than S, then an exception condition is raised:
data exception - substring error.
----------------------------------------------

The end-position (E) is S+L, thus your examples

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

are right.


Regards,
Arno Brinkman
ABVisie

-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-
Firebird open source database (based on IB-OE) with many SQL-99 features :
http://www.firebirdsql.org
http://www.firebirdsql.info
http://www.fingerbird.de/
http://www.comunidade-firebird.org/

Support list for Interbase and Firebird users :
firebird-support@yahoogroups.com

Nederlandse firebird nieuwsgroep :
news://80.126.130.81