Subject Re: [ib-support] SUBSTRING internal function - does not evaluate?
Author Claudio Valderrama C.
""csswa"" <csswa@...> wrote in message
> 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 ')'
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> ::=
| <numeric value expression> <plus sign> <term>
| <numeric value expression> <minus sign> <term>
<term> ::=
| <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

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.

Claudio Valderrama C. - -
Independent developer
Owner of the Interbase® WebRing