Separate City , State and Zipcode from Address SQL

I have to separate the City State and Zip Code from the Adress Column which looks like below.

Some times the City name might be missing or State or Zipcode.

Some times there will be only City,State and Zip in the Adress Column.

Please do help me to resolve this issue.

Adress        开发者_如何学运维                   
PO BOX 28087 RALEIGH, NC 27611      
PO BOX 671 NASHVILLE, TN 37202    

Amrutha, please do mention whether this address you are saying is in text form or you have a sort of form field for entering these...

Anyway, if it's in text form, I mean if these fields are held together as a single string, then SQL will not help. You have to use some sort of text extraction script for that (e.g. perl, awk).

declare @Cities varchar(200)
declare @Count int
declare @Adress varchar(52)
set @Adress = '5129 TWISTED WILLOW CREEDMOOR NC'
declare @i int
set @i = 0
declare @j int
set @j = 1
set @Count = 0
declare @city varchar(20)
set @city = '' 
set @Cities = 'RALEIGH.CARY.FOUR OAKS.CREEDMOOR'
while(@i < =LEN (@cities))
begin
  if(SUBSTRING(@cities,@i,1) = ',')
  set @count = @count+1
     set @i = @i+1
end
while(@j <=5)
begin
declare @s varchar(20)
set @s = PARSENAME(@cities,@j)
  if(PATINDEX('%'+@s+'%',@Adress ) >0)
  set @city = PARSENAME(@cities,@j)

  set @j = @j +1
end


select @city 

I found above solution but i should add all city names to the string @cities. Is their any other way of doing it easily?