Splitting Address in SQL Server -

i able split address in sql server . have sample address

(10396 whispering pines dr frisco tx 75033-3807) 

the street name might have multiple names zip code @ end , state next , city, etc...

i thinking start cutting off zip+4 , work backwards (get zip , remove it. state (always 2 digits) , remove , city.

everything else street address. address have included above how stored in database. tried doing myself know there has better way!

declare @streetaddress varchar(1500) declare @zip varchar(10) declare @state varchar(2) declare @city varchar(250)  set @streetaddress = '10396 whispering pines dr frisco tx 75033-3807'  set @streetaddress =  left(@streetaddress,charindex('-',@streetaddress) - 1) set @zip = right(@streetaddress,5) set @streetaddress = rtrim(replace(@streetaddress,right(@streetaddress,5),'')) set @state = right(@streetaddress,2) set @streetaddress = rtrim(replace(@streetaddress,right(@streetaddress,2),'')) set @city = reverse(left(reverse(@streetaddress),charindex(' ',@streetaddress))) select @streetaddress,@state,@zip,@city 

the output looks this:

|10396 whispering pines dr|tx|75033|frisco| 

try this

declare @test varchar(100)='10396 whispering pines dr frisco tx 75033-3807'; declare @testrev varchar(100)=reverse(@test); declare @pos1 int=charindex(' ',@testrev); declare @pos2 int=charindex(' ',@testrev,@pos1+1); declare @pos3 int=charindex(' ',@testrev,@pos2+1);   declare @part4 varchar(100)=reverse(left(@testrev,@pos1-1)); declare @part3 varchar(100)=reverse(substring(@testrev,@pos1+1,@pos2-@pos1)); declare @part2 varchar(100)=reverse(substring(@testrev,@pos2+1,@pos3-@pos2)); declare @part1 varchar(100)= reverse(substring(@testrev,@pos3,1000));  select @part1, @part3,@part4,@part2 

the result

10396 whispering pines dr    tx     75033-3807   frisco 

edit: approach

the advantage of approach was, parts (separated blanks) in 1 go. make easier continue wiht logic solve multi-word problem...

declare @test varchar(100)='10396 whispering pines dr frisco tx 75033-3807'; declare @testrev varchar(100)=reverse(@test);  declare @casted xml=(select cast('<x>' + replace(@testrev,' ','</x><x>')+'</x>' xml));  select reverse(@casted.value('/x[1]','varchar(max)')) zip       ,reverse(@casted.value('/x[2]','varchar(max)')) state       ,reverse(@casted.value('/x[3]','varchar(max)')) city       ,isnull(reverse(@casted.value('/x[9]','varchar(max)')) + ' ','')       +isnull(reverse(@casted.value('/x[8]','varchar(max)')) + ' ','')           +isnull(reverse(@casted.value('/x[7]','varchar(max)')) + ' ','')       +isnull(reverse(@casted.value('/x[6]','varchar(max)')) + ' ','')           +isnull(reverse(@casted.value('/x[5]','varchar(max)')) + ' ','')           +isnull(reverse(@casted.value('/x[4]','varchar(max)')) + ' ','') therest