FUNCTION addressCleanUp (p_addressLine IN VARCHAR2) RETURN VARCHAR2 IS --=========================================================================== -- Copywright 2003, www.oracletricks.com by joel crainshaw and chet west --=========================================================================== -- DESCRIPTION -- returns a cleaned up "address line" -- --=========================================================================== -- MODIFICATION HISTORY -- Person Date Comments -- --------- ---------- ------------------------------------------- -- chet 10/08/2003 Initial Version --=========================================================================== v_cleanAddress VARCHAR2(2000); BEGIN -- -- Set or working variable -- v_cleanAddress := ' '||p_addressLine||' '; -- -- Make it all upper case -- v_cleanAddress := UPPER(v_cleanAddress); -- -- Convert all commas and periods to spaces -- v_cleanAddress := REPLACE(v_cleanAddress,',',' '); v_cleanAddress := REPLACE(v_cleanAddress,'.',' '); -- -- Replace all the variations of apartment -- v_cleanAddress := REPLACE(v_cleanAddress,'#',' APARTMENT '); v_cleanAddress := REPLACE(v_cleanAddress,'APT',' APARTMENT '); v_cleanAddress := REPLACE(v_cleanAddress,'UNIT',' APARTMENT '); v_cleanAddress := REPLACE(v_cleanAddress,'APARTMENT APARTMENT',' APARTMENT '); v_cleanAddress := REPLACE(v_cleanAddress,'APARTMENT APARTMENT',' APARTMENT '); v_cleanAddress := REPLACE(v_cleanAddress,'APARTMENT APARTMENT',' APARTMENT '); v_cleanAddress := REPLACE(v_cleanAddress,'APARTMENT APARTMENT',' APARTMENT '); -- v_cleanAddress := REPLACE(v_cleanAddress,' APARTMENT ', ' APT '); -- -- Replace all the variations of suite -- v_cleanAddress := REPLACE(v_cleanAddress,'SUITE',' STE '); -- -- Replace all the variations of post office box -- v_cleanAddress := REPLACE(v_cleanAddress,'P O BOX',' POBOX '); v_cleanAddress := REPLACE(v_cleanAddress,'PO BOX',' POBOX '); v_cleanAddress := REPLACE(v_cleanAddress,'POB ',' POBOX '); v_cleanAddress := REPLACE(v_cleanAddress,'POST OFFICE BOX',' POBOX '); -- -- Replace all the variations of direction indicators -- v_cleanAddress := REPLACE(v_cleanAddress,' N W ',' NW '); v_cleanAddress := REPLACE(v_cleanAddress,' N E ',' NE '); v_cleanAddress := REPLACE(v_cleanAddress,' S W ',' SW '); v_cleanAddress := REPLACE(v_cleanAddress,' S E ',' SE '); v_cleanAddress := REPLACE(v_cleanAddress,' NORTHWEST ',' NW '); v_cleanAddress := REPLACE(v_cleanAddress,' NORTHEAST ',' NE '); v_cleanAddress := REPLACE(v_cleanAddress,' SOUTHWEST ',' SW '); v_cleanAddress := REPLACE(v_cleanAddress,' SOUTHEAST ',' SE '); v_cleanAddress := REPLACE(v_cleanAddress,' NO ',' N '); v_cleanAddress := REPLACE(v_cleanAddress,' SO ',' S '); v_cleanAddress := REPLACE(v_cleanAddress,' SOU ',' S '); v_cleanAddress := REPLACE(v_cleanAddress,' NORTH ',' N '); v_cleanAddress := REPLACE(v_cleanAddress,' SOUTH ',' S '); v_cleanAddress := REPLACE(v_cleanAddress,' EAST ' ,' E '); v_cleanAddress := REPLACE(v_cleanAddress,' WEST ' ,' W '); -- -- Replace 1st, 2nd, 3rd, Nth -- v_cleanAddress := REPLACE(v_cleanAddress,'1 ST ' ,'1 '); v_cleanAddress := REPLACE(v_cleanAddress,'1ST ' ,'1 '); v_cleanAddress := REPLACE(v_cleanAddress,'1 TH ' ,'1 '); v_cleanAddress := REPLACE(v_cleanAddress,'1TH ' ,'1 '); v_cleanAddress := REPLACE(v_cleanAddress,' FIRST ' ,' 1 '); v_cleanAddress := REPLACE(v_cleanAddress,'2 ND ' ,'2 '); v_cleanAddress := REPLACE(v_cleanAddress,'2ND ' ,'2 '); v_cleanAddress := REPLACE(v_cleanAddress,' SECOND ' ,' 2 '); v_cleanAddress := REPLACE(v_cleanAddress,'2 TH ' ,'2 '); v_cleanAddress := REPLACE(v_cleanAddress,'2TH ' ,'2 '); v_cleanAddress := REPLACE(v_cleanAddress,'3 RD ' ,'3 '); v_cleanAddress := REPLACE(v_cleanAddress,'3RD ' ,'3 '); v_cleanAddress := REPLACE(v_cleanAddress,'3 TH ' ,'3 '); v_cleanAddress := REPLACE(v_cleanAddress,'3TH ' ,'3 '); v_cleanAddress := REPLACE(v_cleanAddress,' THIRD ' ,' 3 '); v_cleanAddress := REPLACE(v_cleanAddress,'4 TH ' ,'4 '); v_cleanAddress := REPLACE(v_cleanAddress,'4TH ' ,'4 '); v_cleanAddress := REPLACE(v_cleanAddress,' FOURTH ' ,' 4 '); v_cleanAddress := REPLACE(v_cleanAddress,'5 TH ' ,'5 '); v_cleanAddress := REPLACE(v_cleanAddress,'5TH ' ,'5 '); v_cleanAddress := REPLACE(v_cleanAddress,' FIFTH ' ,' 5 '); v_cleanAddress := REPLACE(v_cleanAddress,'6 TH ' ,'6 '); v_cleanAddress := REPLACE(v_cleanAddress,'6TH ' ,'6 '); v_cleanAddress := REPLACE(v_cleanAddress,' SIXTH ' ,' 6 '); v_cleanAddress := REPLACE(v_cleanAddress,'7 TH ' ,'7 '); v_cleanAddress := REPLACE(v_cleanAddress,'7TH ' ,'7 '); v_cleanAddress := REPLACE(v_cleanAddress,' SEVENTH ' ,' 7 '); v_cleanAddress := REPLACE(v_cleanAddress,'8 TH ' ,'8 '); v_cleanAddress := REPLACE(v_cleanAddress,'8TH ' ,'8 '); v_cleanAddress := REPLACE(v_cleanAddress,' EIGHTH ' ,' 8 '); v_cleanAddress := REPLACE(v_cleanAddress,'9TH ' ,'9 '); v_cleanAddress := REPLACE(v_cleanAddress,'9 TH ' ,'9 '); v_cleanAddress := REPLACE(v_cleanAddress,' NINTH ' ,'9 '); v_cleanAddress := REPLACE(v_cleanAddress,'0 TH ' ,'0 '); v_cleanAddress := REPLACE(v_cleanAddress,'0TH ' ,'0 '); v_cleanAddress := REPLACE(v_cleanAddress,'TENTH ' ,'10 '); v_cleanAddress := REPLACE(v_cleanAddress,'ELEVENTH ' ,'10 '); v_cleanAddress := REPLACE(v_cleanAddress,'TWELFTH ' ,'10 '); v_cleanAddress := REPLACE(v_cleanAddress,'THIRTEENTH ' ,'10 '); v_cleanAddress := REPLACE(v_cleanAddress,'TWENTYTH ' ,'20 '); v_cleanAddress := REPLACE(v_cleanAddress,'THIRTYTH ' ,'30 '); v_cleanAddress := REPLACE(v_cleanAddress,'FORTIETH ' ,'40 '); v_cleanAddress := REPLACE(v_cleanAddress,'FIFTIETH ' ,'50 '); v_cleanAddress := REPLACE(v_cleanAddress,'SIXTIETH ' ,'60 '); v_cleanAddress := REPLACE(v_cleanAddress,'SEVENTIETH ' ,'70 '); v_cleanAddress := REPLACE(v_cleanAddress,'EIGHTIETH ' ,'80 '); v_cleanAddress := REPLACE(v_cleanAddress,'NINETIETH ' ,'90 '); -- -- Replace common street identifiers with abbreviations -- v_cleanAddress := REPLACE(v_cleanAddress,' SAINT ', ' ST '); v_cleanAddress := REPLACE(v_cleanAddress,' STREET ', ' ST '); v_cleanAddress := REPLACE(v_cleanAddress,' STR ', ' ST '); v_cleanAddress := REPLACE(v_cleanAddress,' STRT ', ' ST '); v_cleanAddress := REPLACE(v_cleanAddress,' ROAD ', ' RD '); v_cleanAddress := REPLACE(v_cleanAddress,' AVENUE ', ' AVE '); v_cleanAddress := REPLACE(v_cleanAddress,' AV ', ' AVE '); v_cleanAddress := REPLACE(v_cleanAddress,' COURT ', ' CT '); v_cleanAddress := REPLACE(v_cleanAddress,' BUILDING ', ' BLDG '); v_cleanAddress := REPLACE(v_cleanAddress,' BOULEVARD ', ' BLVD '); v_cleanAddress := REPLACE(v_cleanAddress,' LANE ', ' LN '); v_cleanAddress := REPLACE(v_cleanAddress,' CIRCLE ', ' CIR '); v_cleanAddress := REPLACE(v_cleanAddress,' DRIVE ', ' DR '); v_cleanAddress := REPLACE(v_cleanAddress,' PLACE ', ' PL '); v_cleanAddress := REPLACE(v_cleanAddress,' ROUTE ', ' RT '); v_cleanAddress := REPLACE(v_cleanAddress,' TERRACE ', ' TERR '); v_cleanAddress := REPLACE(v_cleanAddress,' HIGHWAY ', ' HWY '); v_cleanAddress := REPLACE(v_cleanAddress,' PARKWAY ', ' PKWY '); v_cleanAddress := REPLACE(v_cleanAddress,' POINT ', ' PT '); v_cleanAddress := REPLACE(v_cleanAddress,' MOUNT ', ' MT '); v_cleanAddress := REPLACE(v_cleanAddress,' FLOOR ', ' FL '); -- -- Get rid of multi-spaces -- v_CleanAddress := singleSpace(v_CleanAddress); -- -- Get rid of symbols -- v_CleanAddress := noSymbols(v_CleanAddress, LENGTH(v_CleanAddress)); -- -- Get rid of ALL spaces -- -- v_CleanAddress := REPLACE(v_CleanAddress,' ',''); -- RETURN v_CleanAddress; END addressCleanUp; --==--==--==--==--==--==--==--==--==--==--==--==--==--==--==--==--==--==--==--== --==--==--==--==--==--==--==--==--==--==--==--==--==--==--==--==--==--==--==--== --==--==--==--==--==--==--==--==--==--==--==--==--==--==--==--==--==--==--==--==