Not to come across as a doomsayer, but just as a warning, you need to be aware that many, many top notch IT departments have addressed this problem with very little success!
If the nomenclature for this kind of thing was limited to the more common
Avenue
Drive
Highway
Lane
Road
Street
this kind of problem could come close to being doable, but the possibilities simply go on and on and on and on and on, making it nearly impossible to automate successfully!
While in school I did some contract work, through an agency, for a number of the biggest banks in the country. The scenario involved BankA purchasing BankB, and BankA needing to make the BankB addresses come into compliance with their own address formats. All of the BankAs were national, multi-billion dollar institutions, with tremendous IT departments, and none of them were able to automate the task enough to do even the simplest 'cleanup' reliably! The problem is that…in addition to the designations above…other commonplace designations include, but are not limited to:
Alley
Annex
Bottom
Boulevard
Bypass
Byway
Causeway
Circle
Close
Court
Crescent
Crossing
Freeway
Gardens
Gateway
Green
Heights
Hollow
Landing
Loop
Muse
Parkway
Pass
Passage
Pike
Place
Plaza
Ridge
Ring
Row
Square
Terrace
Trace
Trail
Viaduct
Way
As I said, these organizations had crackerjack IT teams, but in the end, after hundreds of hours of development time, every team ended up recommending the using of teams of data entry workers who simply eyeballed the original designations/abbreviations and manually made the needed changes!
Linq ;0)>
The problem with making anything foolproof...is that fools are so darn ingenious!
All posts/responses based on Access 2003/2007