This blog post is a result of the currently active thread at MSDN T-SQL forum

Parsing Address field to its components

The question of parsing a row data with bad formatted addresses often comes in the SQL newsgroups. Unfortunately, there is no universal 100% bullet-proof solution that covers all possible scenarios. However, for a common scenario of City, State Zip the parsing can be done using the following technique:

declare @t table (Address varchar(max))
insert into @t 
union all select
'LEESBURG, FL 34788'
union all select
'COLUMBUS, OH 43221'
union all select
union all select
union all select
union all select

select Address, F.City, F10.State, F4.Zip from @t 
cross apply (select charindex(', ', Address) AS CommaPos) F0
cross apply (select case when CommaPos = 0 then 'Bad Address' else SUBSTRING(Address,1,CommaPos - 1) end as City) F
cross apply (select case when CommaPos = 0 then NULL else ltrim(substring(Address,CommaPos+1, len(Address))) end as Rest) F2
cross apply (select PATINDEX('%[0-9]%',Rest) as DigitPos) F3
cross apply (select case when DigitPos > 0 then substring(Rest, DigitPos, len(Rest)) end as Zip) F4
cross apply (select case when Zip Is not NULL then replace(Rest,Zip,'') else Rest end as State) F10

Please, note, that I used CROSS APPLY technique to divide the parsing job into several related steps by first taking the City, then the rest of the string, then parsing the remainder.

I learned this CROSS APPLY technique from this excellent blog post by Brad Schulz

Cool CROSS APPLY tricks – part 2 That’s when this technique finally became very familiar for me. I want to tell here that I always enjoy reading Brad’s blogs because of the humorous style of his writing.

*** Remember, if you have a SQL related question, try our Microsoft SQL Server Programming forum or our Microsoft SQL Server Admin forum