Selecting the State Code

[TheBigMaple]

So I have a bunch of address that have:

Street Address City St Zip

Nothing is standardized accept the order. There could be a comma between city and state and there may or may not be a space between the state and zip. The zip also varies in size (#####, #####-####, or #########). Sometimes the zip is not there at all. I am looking for a way to identify the State or at least the position that is starts at. Any help would be awesome.

[orange]

Well you could start by assigning a unique identifier to every record.

You can get a zip - state list for free here http://www.zipcodeworld.com/zipcodefree.htm
With zip you can get state
Once done
For zips:
go to the far end of the record and get the last 10 characters.
Create another table that has the unique number, and the last 10 chars.
Now, parse those 10 characters from right end . goal is unique number and proper zip

a) could by state + 5 digit zip
b) could be part of state and space + 5 digit zip
c) could be space or Alpha + 9 digit zip
d) could be 5 DIGITS + DASH +4 digits
e) maybe more???


Similar approach to State and remaining fields.

Bottom line is that the approach probably depends on what you want to do with the result.
You don't say you need the whole address... etc.
Good luck