SQL Script to extract address information into individual fields

I had a need to write a script or a program that would extract an address that is stored in one field and break it out into its parts. Address, Address2, City, State and Postal Code(zip code).

It’s a real bugger to do in a single SQL statement, I had one but it was really really ugly. I mean, wing man, take one for the team, dating the hot girls friend kind of ugly.

So, I made this simple script that you can either run outright to convert your data or you can easily modify this into a procedure.

Background:

I created the new fields on the original table and I also added a field I called SiteAddressBackup. I did an update into this field before running this script in order run against real live data and still have it safe and sound if I mucked things up. I suggest you do the same to preserve your data.

The script. Should be easy to follow.


--Seekford script to extract addresses
--Tested on 2100 real addresses from early production db
--examples
--1234 GREENVIEW DR SW,SUITE # 222,ROCHESTER, MN 55902-1080
--1555 SAM NUNN BLVD,PERRY, GA 31069-1954
   
DECLARE AddressUpdate CURSOR FOR         
SELECT MyOrdersID,SIteAddress
FROM tMyOrders
ORDER BY MyOrdersID
        
DECLARE @ID INT        
DECLARE @SiteAddress VARCHAR(255)        

update tMyOrders set SiteAddress = SiteAddressBackup
        
OPEN AddressUpdate        
        
FETCH NEXT FROM AddressUpdate         
INTO @ID, @SiteAddress
        
WHILE @@FETCH_STATUS = 0        
BEGIN  

	
declare @Addr as varchar(100)
declare @Addr2 as varchar(100)
declare @City as varchar(100)
declare @State as varchar(100)
declare @Postal as varchar(100)
declare @Temp as varchar(100)

--extract the postal and state
set @Temp = rtrim(ltrim(SUBSTRING(@SiteAddress,( LEN(@SiteAddress) - CHARINDEX(',', REVERSE(@SiteAddress), 1)) +2 ,100)))
set @Postal = SUBSTRING(@Temp,( LEN(@Temp) - CHARINDEX(' ', REVERSE(@Temp), 1)) +2 ,100)
set @State = SUBSTRING(rtrim(ltrim(@Temp)),0,( LEN(@Temp) - CHARINDEX(' ', REVERSE(@Temp), 1))+1)

--extract the city )pulls everything but the state and zip
set @Temp = SUBSTRING(@SiteAddress,0,( LEN(@SiteAddress) - CHARINDEX(',', REVERSE(@SiteAddress), 1))+1  )

set @City = SUBSTRING(@Temp,( LEN(@Temp) - CHARINDEX(',', REVERSE(@Temp), 1)) +2 ,100)

--reparse
set @Temp = SUBSTRING(@temp,0,len(@temp) - len(@city) )
set @Addr2 = SUBSTRING(@Temp,( LEN(@Temp) - CHARINDEX(',', REVERSE(@Temp), 1)) +2 ,100)

if(LEN(@Addr2) =0) 
begin
set @Addr = SUBSTRING(@temp,0,len(@temp) - len(@Addr2)+1 )
end
else
begin
set @Addr = SUBSTRING(@temp,0,len(@temp) - len(@Addr2) )
end


update tMyOrders set SiteAddress = @Addr,siteaddress2=@Addr2,SiteCity = @City,SiteState = @State , SitePostalCode = @Postal
where MyOrdersID = @ID 
     
FETCH NEXT FROM AddressUpdate         
INTO @ID, @SiteAddress
 
END        
        
CLOSE AddressUpdate        
DEALLOCATE AddressUpdate   

--Query to show them broken out against the original
SELECT [MyOrdersID]      ,
SiteAddress,SiteAddress2,SiteCity,SiteState,SitePostalCode  ,siteaddressbackup   
  FROM [tMyOrders]
 

Leave a Reply

Your email address will not be published. Required fields are marked *