parse address - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

parse address

Author

Topic

neeners
Starting Member

2 Posts

Posted - 2009-11-16 : 14:27:51

I have the street number and street address in SQL server 2005 in one column. (Ex. 410 MELINDA ST).

How can I easily separate the street number, name, and type?

I also have addresses like this: 1102 S GREVILEA AVE APT
& 3000 N G ST. The solution would need to work for these addresses as well.

Thanks!

neeners
Starting Member

2 Posts

Posted - 2009-11-16 : 14:38:27

Found another long address like: 4391 E 117TH ST APT 12

X002548
Not Just a Number

15586 Posts

Posted - 2009-11-16 : 16:02:27

No miracles....

You will not find a 100% solution for this

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-11-17 : 02:22:25

What is the expected result of the sample data?

See if this is what you want



select 
	data,
	street,
	substring(data,len(street)+1,len(data)-len(street)-len([type])) as [name],
	case when [type] not like '%[^0-9]%' then 
		right(substring(data,1,len(data)-len([type])-1),charindex(' ',reverse(substring(data,1,len(data)-len([type])-1)))-1) +[type]
	else
		[type]
	end as [type]
	
from
(
select 
	data,
	left(data,charindex(' ',data)-1) as street,
	right(data,charindex(' ',reverse(data))-1) as [type] from
(
select '410 MELINDA ST' as data union all
select '1102 S GREVILEA AVE APT'union all
select '4391 E 117TH ST APT 12'
) as t
) as t

Madhivanan

Failing to plan is Planning to fail

briceeric
Starting Member

3 Posts

Posted - 2011-03-26 : 18:51:05

For anyone looking to perform something similar you may want to check out the SqlAddress product from Ambient Concepts which is designed to parse address delivery lines at the SQL Server level. It provides SQLCLR functions to make this easy.

unspammed

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources