Devide 1 feild into 3 feilds!

Hi there!

There is a feild in my table that contains the city, state and zip, all in the same feild. I was wondering how would be the best way to devide all that info up into 3 feilds? i really don't know how I would go about it since there are cities that can be comosed of 2 or more words. Here is some sample data. I hope someone can help.

SOUTH EL MONTE CA91733617
BOSSIER LA71172
GARDENA CA90249107
MILWAUKEE WI53216
PARIS IL61944
DUQUOIN IL62832
REDWOOD FALLS MN56283
AUBURN ME04210

IRWINDALE CA91706048
PORTLAND OR97202901
PORTLAND OR972028901
ANAHEIM CA928071735
KENT WA98032
CRYSTAL LAKE IL60014611Why don't u try to extract from the right end..i.e zip first ..then State and city...since zip and state are single words it should work...|||I have tried to extract ur reuirement...I think this would meet ur requirement...

create table test(field varchar(100))
go
insert into test
select 'REDWOOD FALLS MN56283'
select 'SOUTH EL MONTE CA91733617' Union all
select 'BOSSIER LA71172' Union all
select 'GARDENA CA90249107'

go

select reverse(substring(reverse(field),charindex(' ',reverse(field))+1,len(field))) 'City',
substring(reverse(substring(reverse(field),1,CHARI NDEX ( ' ' , reverse(field) )-1)),1,2) 'State',
substring(reverse(substring(reverse(field),1,CHARI NDEX ( ' ' , reverse(field) )-1)),3,len(reverse(substring(reverse(field),1,CHARI NDEX ( ' ' , reverse(field) )-1)))) 'Zip'
from test

This code may not me the best performing query...I just wanted the query work...