I'd like to standardize all the addresses entered into my database. for example, people here would enter "Street" as "ST", "St.", "St" or any variation of this, but i would want it just as "ST". other things that i wish to standardize are the exclusion of all punctuation, etc etc.
I've entertained the idea of using a different field for every component of the addresss (i.e address_number, address_direction, address_name, address_suffix, etc) but the database has been in use for several years now and I don't want to change this part.
In other words, regardless of how an address is entered, it should always come out in the following format:
1234 W TEST ST
PHOENIX AZ 85006
is this possible?
Trim ( Filter ( Substitute ( Upper ( AddressField ) & " " ; " STREET " ; " ST" ) ; "0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZ# " ) )
Might do the trick.