variable length strings, update queries

S

Scott Phelps

I have a field called ADDRESS and it contains a street
number or PO Box number, street name, street type, city,
state, and zipcode. Typical record entries looks like
this:

110 HOLLY TREE RD. RIEGELWOOD NC 28456
129 COLONNADE DR. PEACHTREE CITY GA. 30269
1301 CROZIER DR., APT. B REDSTONE ARSENAL, ALA. 35808
1515 CHERRYTOWN RD. (HB) 30 TEMPLE PLACE IRVINGTON NJ 07111
1638 LENNON RD DELCO, NC 28436

What I'm trying to do is place the city, state, and zip
portions into three new fields, CITY, STATE, and ZIP.

There's 40000+ records I have to work with and I've tried
several string parsing techniques, but still not getting
the most efficient result. Does anybody know of another
way to accomplish this?

Scott
 
J

John Vinson

I have a field called ADDRESS and it contains a street
number or PO Box number, street name, street type, city,
state, and zipcode. Typical record entries looks like
this:

110 HOLLY TREE RD. RIEGELWOOD NC 28456
129 COLONNADE DR. PEACHTREE CITY GA. 30269
1301 CROZIER DR., APT. B REDSTONE ARSENAL, ALA. 35808
1515 CHERRYTOWN RD. (HB) 30 TEMPLE PLACE IRVINGTON NJ 07111
1638 LENNON RD DELCO, NC 28436

What I'm trying to do is place the city, state, and zip
portions into three new fields, CITY, STATE, and ZIP.
Click to expand...

Unfortunately, given that data, I can't imagine any *rigorous* way to
automate it. The Zip is easy:

Right(Trim([ADDRESS]), 5)

unless you have some Zip+4; the State is fairly easy - the next to
last "token" in the string - unless you have "NORTH DAKOTA" written
out. But the city is almost impossible - in your next to last example,
if you apply the logic that works for "Peachtree City" you would store
a city "PLACE IRVINGTON"; if you apply logic that works for "SALT LAKE
CITY" you would get a city named "B REDSTONE ARSENAL". The
inconsistant use of commas merely makes matters worse.

About the best I can suggest is to write code to parse the string
which will:

- Replace all commas with a null string
- Use the Split() function to move all the "words" - delimited by
blanks - into elements of an array
- Store the last element of the array in Zip, the next to last in
State, and the third from last in City; and concatenate the rest into
Address.

You'll then need to group by Zip, and by State, and by City and
correct the inevitable anomalies by hand.
 
Top