Hi Todd,

> My stored procedure seems to work. I am populating a double
> precision column with data from the char column. I check each
> character before adding it to the double precision column. Actually
> I add them to another char column and then do a cast at the end of
> the procedure.

I liked your puzzle, so I wrote an SP that converts chars into doubles
if possible, and (if necessary) ignores any trailing non-digits, so
12XB yields 12.

Having read your latest postings, I'm not so sure anymore if this is
exactly what you want (I now get the impression you just drop any
non-digits so even A1Q2U returns 12), but I'll let it follow anyway.


If you declare the standard UDF substrlen in your db, and then this:

create procedure char7_to_double( charvar char( 7 ) )
returns ( doublevar double precision )
as
declare variable len smallint;
begin
doublevar = null;
len = 7; // could generalize this using strlen
while ( len > 0 ) do
begin
doublevar = cast ( charvar as double precision );
exit; // upon success; line is skipped if cast failed
when gdscode convert_error /* or sqlcode -413 */ do
begin
len = len - 1;
if ( len > 0 ) then charvar = substrlen( charvar, 1, len );
end
end
end


Now, in your before insert/update triggers, you simply say

execute procedure char7_to_double New.QuickKey
returning_values New.QuickNum; // *not* select ... into

( In "after" triggers, you need a searched update because you can't
assign to New. Well, you can, but it has no effect. I do it at
least once a month and then wonder why things don't work ;-) )

Many people don't like EXIT. If you're one of those, you need to add
an extra var and some lines to check for success or failure.


Greetings,
Paul