>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 : )

I use the following code to grab a number in paraenthise (). You may be able to adapt it to your application by sarching backwards until you find the beginning of the zip code.

I hope it helps.


SCAN
nlenght = LEN(ALLT(NAME))
nbegin = 1
nend = 1
lfind = .F.
FOR x = 1 TO nlenght
ctemp = SUBSTR(NAME,x,1)
IF ctemp = "("
nbegin = x + 1
lfind = .T.
ENDIF
IF ctemp = ")"
nend = x
ENDIF
NEXT x
ccust = LEFT(NAME,(nbegin - 2))
cco_num = SUBSTR(NAME,nbegin,(nend - nbegin))
IF ! INLIST(LEFT(ALLTRIM(cco_num),1),"0","1","2","3","4","5","6","7","8","9")
cco_num = "-NF-"
ENDIF
IF ! lfind
cco_num = "-NF-"
ENDIF
REPLACE co_num WITH UPPER(PADL(ALLT(cco_num),7," ")), customer WITH ALLT(ccust)
ENDSCAN