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

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
^

Cheers,
Jono