""csswa"" <csswa@...> wrote in message
news:a82c66+a51i@......

>
> So basically it appears that if <POS> is not a simple integer value
> then SUBSTRING will fail?? In the FB1 release notes it says <POS>
> must evaluate to an integer which to my mind suggests it can be an
> expression or variable...

First, make it work, then enhance it. The current definition is:
SUBSTRING '(' value FROM pos_short_integer ')'
or
SUBSTRING '(' value FROM pos_short_integer FOR nonneg_short_integer ')'

For now, they are constants.

The standard says:
<character substring function> ::=
SUBSTRING <left paren> <character value expression> FROM <start position>
[ FOR <string length> ] <right paren>

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

<numeric value expression> ::=
<term>
| <numeric value expression> <plus sign> <term>
| <numeric value expression> <minus sign> <term>
<term> ::=
<factor>
| <term> <asterisk> <factor>
| <term> <solidus> <factor>
<factor> ::=
[ <sign> ] <numeric primary>
<numeric primary> ::=
<value expression primary>
I <numeric value function>

In plain words, it tells you that values can be preceded by a sign and that
the four arithmetic operations are allowed with multiplication and division
having priority, only that the SQL committee doesn't like simple
explanations.
:-)

The crux is "factor" that takes you to "numeric primary" and from that
point, to two alternatives as shown above:

<value expression primary> ::=
<unsigned value specification>
| <column reference>
I <set function specification>
I <scalar subquery>
I <case expression>
| <left parent> <value expression> <right paren>
| <cast specification>

<numeric value function> ::=
<position expression>
| <extract expression>
I <length expression>

So, in the worst case, you can embed a CASE statement inside the
"starting_pos" or the "length" parameters in substring(). Being more
realistic, in the future we can support field evaluation and parameter
evaluation for the starting_pos argument.

C.
--
Claudio Valderrama C. - http://www.cvalde.com - http://www.firebirdSql.org
Independent developer
Owner of the Interbase® WebRing