Subject | Re: [ib-support] SUBSTRING internal function - does not evaluate? |
---|---|

Author | Claudio Valderrama C. |

Post date | 2002-03-30T04:07:15Z |

""csswa"" <csswa@...> wrote in message

news:a82c66+a51i@......

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

news:a82c66+a51i@......

>First, make it work, then enhance it. The current definition is:

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

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