Multiple Pass String Parsing

[giswzrd]

I have a database (Oracle8) with two tables of street addresses one of which is updated by another department. The other department uses abbrevations for Street, Avenue, etc. My table does not. I'm trying to generate a report with CR9 of differences in names and numbers only (i.e. - 123 Stetson Road vs 123 Stetson Rd)

The code I'm trying to use is -

LOCAL numberVar X:=1;

WHILE X=1 DO
(X:=0;
(IF InStr ({C.ADDR}, ' ST') <> 0 THEN
(X:=1;
IF Mid ({C.ADDR}, InStr ({C.ADDR}, ' ST'))=' ST' THEN
Replace ({C.ADDR}, ' ST', ' STREET')
ELSE
Replace ({C.ADDR}, ' ST', ' STREET ');
)

ELSE

IF InStr ({C.ADDR}, ' RD') <> 0 THEN
(X:=1;
IF Mid ({C.ADDR}, InStr ({C.ADDR}, ' RD'))=' RD' THEN
Replace ({C.ADDR}, ' RD', ' ROAD')
ELSE
Replace ({C.ADDR}, ' RD', ' ROAD ')
)
))

I get the Error Message - The loop was evaluated more than the maximum number of times allowed.

I think I get the Error because every cycle of the loop reinitializes the {C.ADDR}. I'm stumped as to how to keep the changes made on each iteration.

Any help would be greatly appreciated.

 

[SmpTraining]

I would suggest you expand both addresses into their fullest form and then compare the two results. The expansion forumla would look something like the following. I appreciate you will come up against many other abbreviations when you test this but the principal is sound (I believe!).

// Convert an existing address line such that all abbreviations are expanded.

StringVar StringToCheck := {Customer.Address1};

// Expand abbreviations:

// Start with the easy ones - those with spaces before and after the abbreviation
StringToCheck := Replace (StringToCheck, " St ", " Street ");
StringToCheck := Replace (StringToCheck, " Rd ", " Road ");
StringToCheck := Replace (StringToCheck, " Av ", " Avenue ");
StringToCheck := Replace (StringToCheck, " Ave ", " Avenue ");

//Next, check the endings
if right(StringToCheck, 3) = " St" then 
    StringToCheck := Replace (StringToCheck, " St", " Street");
if right(StringToCheck, 3) = " Rd" then 
    StringToCheck := Replace (StringToCheck, " Rd", " Road");
if right(StringToCheck, 3) = " Ave" then 
    StringToCheck := Replace (StringToCheck, " Ave", " Avenue");

// Retuurn the result
StringToCheck

Steve Phillips, Crystal Consultant

 

[giswzrd]

Thanks Steve for pointing me in the correct direction. Sometimes I can't see the trees for the forest!

Pat Rains, GIS Network Administrator