At 02:12 AM 1/03/2004 +0000, you wrote:

>Hi all,
>I am attempting to use SUBSTRING to iterate through a string pulling
>out numerals only within the context of a table trigger.
>
>Unfortunately, it appears that the variable i within the substring
>function call is an unknown token. A semi-colon is similarly
>unknown. This seems in contradiction to the documentation which
>states "<pos> must evaluate to an integer" and i is at <pos>.

Yup. Unfortunately <pos> must either *be* an integer or be an expression
(in brackets) that evaluates to an integer. I've been bitten by that one, too.

>Any ideas? Here is my trigger definition, which if the i at <pos> is
>substuted for a constant (eg. 5) compiles and runs. Also, if anyone
>knows a better way to return numbers only within a phone_no string
>(without a UDF) please feel free to hit me with it.
>
>ALTER TRIGGER ADDRESS_KEYS
>BEFORE INSERT OR UPDATE POSITION 0
>as
> declare variable i integer;
> declare variable new_ph varchar(50);
> declare variable ch char(1);
>begin
> new_ph = '';
> if ((new.HOME_PHONE IS NOT NULL) AND (new.HOME_PHONE <>
>old.HOME_PHONE)) then
> begin
> i = 0;
> while (i <= 50) do
> begin
> i = i + 1;
> ch = substring(new.HOME_PHONE from i for 1);
> execute procedure chr_is_num :ch returning_values :ch;
> if (ch is not null) then new_ph = new_ph || ch;
> end
> new.HOME_PHONE = new_ph;
> end
>end
> ^

Here's how I've done this. Let's use your embedded chr_is_num call,
although I just use the standard ascii_val UDF.

ALTER TRIGGER ADDRESS_KEYS
BEFORE INSERT OR UPDATE POSITION 0
as
declare variable i integer;
declare variable new_ph varchar(50);
declare variable ch char(1);
begin
new_ph = '';
if ((new.HOME_PHONE IS NOT NULL) AND (new.HOME_PHONE <>
old.HOME_PHONE)) then
begin
i = 0;

while (i <= 50) do
begin
i = i + 1;
ch = substring(new.HOME_PHONE from 1 for 1);
execute procedure chr_is_num :ch returning_values :ch;
if (ch is not null) then
new_ph = new_ph || ch;
new.HOME_PHONE = substring(new.HOME_PHONE from 2);
end
new.HOME_PHONE = new_ph;
end
end
^

/heLen