>I need an example via fox sql, of (if any) pulling the zip-code out of a city,state zip field unfortunately the data has been captured in this one field for years and I need to break it out. There are no constants i.e. (city, state zip), (city state zip), (city-state-zip) or (city, state zip). The zip can be from 5 to 9 numbers in char. Help : )
Can you show some examples? I have Address Standardization application, which does similar operations.
Basically, I would do:
1) Reduce spaces by reduce function from FoxTools
2) Get rid of all unwanted chars, like &$@~! , etc.
3) Parse string by words
4) Analyze each word. Say, if word is 2 chars and could be found in States Lookups table, it's a state; if word is all digits and either 5 or 9 digits, it's a ZIP, etc.
You still would have some exceptions, which would require manual user intervention (say, Canadian ZipCodes, or misspelled State, etc.)
If it's not broken, fix it until it is.
My Blog