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

Our new SQL Server Forums are live! Come on over! We've restricted the ability to create new threads on these forums.

SQL Server Forums
Profile | Active Topics | Members | Search | Forum FAQ
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 Split Address with horrible data
 Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

Yak Posting Veteran

72 Posts

Posted - 01/10/2013 :  13:06:17  Show Profile  Reply with Quote
I am trying to split address into house number and address but my problem is that the data is so random it is proving to be a challenge... Sometimes the data starts with a number some a name etc and there is no consistency. I realize this is going to be no where near perfect but I just want something that will take the house number (if the first part of the address is a number) and put that for house number. Otherwise it all goes into the column for address. Also, if there is a direction split that up, too...

Sample data is:
249 N Governors St
Cherry Tree 564 glover rd
regions lobby
west baptist church
lost rd and oprah st billy s/d
245754 oak tree dr xpost
5643 hill lake way x pinedale dr snap bend s/d off texas

for first record pull out 249 for stno, e for sdir, and the rest for address.

245754, 6138, 5643 would all go into stno and the rest would go into address. I would prefer getting the 564 to go into stno too but I don't know if that is possible.

Edited by - cstokes91 on 01/10/2013 13:21:22

Most Valuable Yak

15732 Posts

Posted - 01/10/2013 :  13:23:26  Show Profile  Visit robvolk's Homepage  Reply with Quote
What about addresses like "15th Street #234"?

How many addresses do you have to process? Anything over 100K, save your sanity and buy software to do it. MelissaData has a few products that are reasonably priced. From my experience cleaning up shit addresses, it's either manual (<10K addresses) or specialized software. SQL is poorly suited for parsing and extracting address information.

There may be some SSIS components that can do the cleanup/parsing. You might want to check PragmaticWorks:
Go to Top of Page

Flowing Fount of Yak Knowledge

8781 Posts

Posted - 01/10/2013 :  13:23:27  Show Profile  Visit webfred's Homepage  Reply with Quote
see here, maybe there is something useful for you:

Too old to Rock'n'Roll too young to die.
Go to Top of Page

Yak Posting Veteran

72 Posts

Posted - 01/10/2013 :  13:34:08  Show Profile  Reply with Quote
I am looking into that now... I have started writing something but I was seeing what people would come up with for this exact situation...

My biggest concern is just doing the best we can with what we have and it doesn't by any means have to be perfect and get everything correct. I just want to grab the ones that have numbers at the beginning then throw that into stno, then if they are immediately followed by a direction, throw them into a direction, then whatever else throw it into the address. Whatever doesn't go in perfectly it doesn't matter because it will appear to be the same for the client, just not reporting.
Go to Top of Page
  Previous Topic Topic Next Topic  
 Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.03 seconds. Powered By: Snitz Forums 2000