Trouble parsing out 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

Trouble parsing out address

Author

Topic

offspring22
Starting Member

38 Posts

Posted - 2009-09-29 : 16:36:27

So I have a field called "address" which I need to break down into direction, street type, name, etc. I have been able to pull out the street dir and type, and remove them from the address field, but with street name I'm having some trouble as some have 1 word, some have 2. Ie, 123 happy dale , and 456 Smile. I can pull out the far right word, but that leaves me with half of the street name. Anyway I can pull out all the alpha characters, or failing that pull out everything after the first set of numbers, and a seperate query to remove them after the fact and leave me with just the house number?

IE, a select where "123 happy dale" becomes "happy dale" and "456 smile" becomes just "smile" etc?

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2009-09-29 : 17:00:59

In regards to stripping numbers or letters from a string you may be able to make use of this function:
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=79083

Be One with the Optimizer
TG

X002548
Not Just a Number

15586 Posts

Posted - 2009-09-29 : 17:08:55

Any chance you can post some sample data and what it's suppose to be parsed into?

In any case, I would think you will only get an 80% automated solution...the other will be manual, or heavily rule based

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

offspring22
Starting Member

38 Posts

Posted - 2009-09-30 : 12:03:29

Sure, I've already parsed out street direction, and type as mentioned. I don't need the address field left in tact after the fact, so I can put say the number and put it into its proper field, then take out the number from the address field leaving it only the street name, if that's easiest. Here's a sample of what I have.

108 Heritage Lake
108 Heritage Lake
108 John
108 Marischal
108 Millarville
108 Niblock
108 Stuart
108 Sunrise
108 Sunset
108 Wooldridge Park
109 Aberdeen

And I'd need to break it out into

108
108
108
108
108
108
108
108
108
108
109

and

Heritage Lake
Heritage Lake
John
Marischal
Millarville
Niblock
Stuart
Sunrise
Sunset
Wooldridge Park
Aberdeen

offspring22
Starting Member

38 Posts

Posted - 2009-09-30 : 12:16:53

There is the occasional street type that is a number as well, as well as numbers longer, or shorter than 3 digits...

ie:

4 Ridge Pointe
42 Sunrise
432 Pine Creek
432 Pine Creek

which would change into

4
42
432
432

and

Ridge Pointe
Sunrise
Pine Creek
Pine Creek

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-10-01 : 02:03:35

Also refer
http://sqlblogcasts.com/blogs/madhivanan/archive/2007/12/18/extract-only-numbers-from-a-string.aspx

Madhivanan

Failing to plan is Planning to fail

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-10-01 : 02:16:21

quote:
Originally posted by offspring22

There is the occasional street type that is a number as well, as well as numbers longer, or shorter than 3 digits...

ie:

4 Ridge Pointe
42 Sunrise
432 Pine Creek
432 Pine Creek

which would change into

4
42
432
432

and

Ridge Pointe
Sunrise
Pine Creek
Pine Creek


SELECT RTRIM(LEFT(Address,CASE WHEN PATINDEX('[A-Z]%',Address) > 0 THEN PATINDEX('[A-Z]%',Address)-1 ELSE LEN(Address) END)) AS StreetType,
SUBSTRING(Address,CASE WHEN PATINDEX('[A-Z]%',Address) > 0 THEN PATINDEX('[A-Z]%',Address) ELSE NULL END,LEN(Address)) AS type
FROM Table

briceeric
Starting Member

3 Posts

Posted - 2011-03-26 : 18:44:02

For anyone looking to parse address delivery lines in SQL Server, you may want to consider Ambient Concepts SqlAddress which provides a set of SQLCLR functions for making this easy: unspammed

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources