It's not that simple as it seems. Check <B>Parsing a Street Address into Separate Fields</B> Thread #
1144790 >
>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
--sb--