Parse data within one column 1

[jose1024]

I have a column that contains city, state zip data. I need to place city and state in one column and zip in another. The field is 25 characters in length and obviously, the city varies in length within the field. I am using Transact SQL in SQL Server 2000 Query Analyzer. Any suggestions?

Examples:

current setup:
CityStateZip
ANYTOWN, NY 11123

desired result:
CityState Zip
ANYTOWN, NY 11123

 

[nigelrivett]

select left(fld, len(fld) - patinindex('%[^0-9]%',reverse(fld)), right(fld, patinindex('%[^0-9]%',reverse(fld) - 1)
from tbl

Something like that.

(there are sql server forums on this sight)

======================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.