Hi all,

I have a database I am converting to a new application database and I need to move the addresses. Currently the file has addresses in the client file as:

Address
Address2
City
State
Zip5

An example of data in those files looks like this:

15329 SCENIC VIEW DR       NULL             BULLARD     TX     75757
22617 POST OAK DR #A       NULL              FLINT             TX     75762
16628 (TR #1) CR 1100         NULL              FLINT             TX     75762
RT 2 BOX 171                          NULL               BULLARD     TX     75757
159 C R 3327                         PO BOX 163     CUNEY          TX    75759

For the most part, I will probably ignore what is in the Address 2 column as it appears to be a mailing address or a repeat of an apartment number. I can tag the Record Status column in this table as needing verification for address or something for those minor cases.

What I need to do is move the Addresses out to a new table called Location.

LocationID                 UniqueIdentifier
LocationTypeID         Int
AddressNumber       varchar(15)
StreetName                varchar(50)
ApartmentNumber     varchar(10)
PostalCode                 int

Postal Codes are already normalized, so I will have to look those up and replace the proper integer foreign key id.
As I move an address out, I have added a column "LocationID" in the original table so that I can connect the address back. The problem is as you can see the addresses are all formatted free form except there seems to be some consistency with upper case. Plus there are certainly many that are duplicate addresses as those have moved and not updated their addresses.

There are about 2000 rows in this table, so it isn't entirely out of the question to create a form for some data entry person to go through them, but I would like to get it most of the way separated if I can.
Any ideas are appreciated.
Thanks
Tim