T-SQL Code to parse and address [modified]

[korrowna]

Hello, I have an issue with addresses that I need to parse into streetNum, Direction (if it is there), sreetName. The data can look as such: 1234 McKean Street -- Needs to look like StreetNum: 1234, Direction: , StreetName: McKean 1234 Mc Kean Street -- Needs to look like StreetNum: 1234, Direction: , StreetName: Mc Kean 1234 E McKean Street -- Needs to look like StreetNum: 1234, Direction: E, StreetName: McKean 1234 E Mc Kean Street -- Needs to look like StreetNum: 1234, Direction: E, StreetName: Mc Kean (edit I went ahead and made the 526 addresses that include 1/2 to be have an underscore so since there is no space anymore it is easy to query) As far as I can tell those are all the options. There are approximately 600,000 records so it really has to be automated. There can be A LOT of changes in this data as the city may update a large portion of it at any time so I need to be able to do this on a monthly basis. One thing to note I am trying to do this so I can compare this data to another table of 70,000 records to automate a search that I would normally do using a "like" query in Access but I streamlined that data as it was only 70k records with a turnover of about 50 a month so that is easy enough and now I need to streamline this data so I can compare the two tables. That data is in this format: streetNumber 1324, 1324-28, 1324-1/2 direction N,S,E,W streetName McKean (I went through by hand and took all the spaces out) I post this information just in case it is easier to do this another way even though it would be really nice to have that data parsed but because of it being different and the only delimiter being a space makes it difficult to parse. Any help would be much appreciated. Thank you, George

modified on Friday, December 4, 2009 10:27 AM

[Niladri_Biswas]

Let me know if it helps Sample data

declare @t table(completeaddress varchar(100))
insert into @t
select '1234 McKean Street' union all select '1234 McKean Street' union all
select '1234 E McKean Street' union all select '1234 E McKean Street'

Query

select
StreetNum = 'StreetNum : ' + SUBSTRING(completeaddress,1,4) +','
,Direction = 'Direction : ' +

			case when ltrim(rtrim(SUBSTRING(completeaddress,5,2))) In('N','S','E','W') 
				 then SUBSTRING(completeaddress,5,2) else '' end + ','
,StreetName = 'StreetName : ' + 

case when ltrim(rtrim(SUBSTRING(completeaddress,5,2))) In('N','S','E','W')
then SUBSTRING(completeaddress,7,LEN(completeaddress)) 
else SUBSTRING(completeaddress,6,LEN(completeaddress)) end	

from @t

Output: StreetNum Direction StreetName

StreetNum : 1234, Direction : , StreetName : McKean Street
StreetNum : 1234, Direction : , StreetName : McKean Street
StreetNum : 1234, Direction : E, StreetName : McKean Street
StreetNum : 1234, Direction : E, StreetName : McKean Street

:)

Niladri Biswas