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.
Author |
Topic |
cstokes91
Yak Posting Veteran
72 Posts |
Posted - 2013-01-10 : 13:06:17
|
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 rdregions lobbywest baptist churchlost rd and oprah st billy s/d245754 oak tree dr xpostTOYOTA DEALERSHIP ON HAMILTON6138 HAPPY LN5643 hill lake way x pinedale dr snap bend s/d off texasoutput: 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. |
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2013-01-10 : 13:23:26
|
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: http://www.pragmaticworks.com/Products.aspx |
|
|
webfred
Master Smack Fu Yak Hacker
8781 Posts |
|
cstokes91
Yak Posting Veteran
72 Posts |
Posted - 2013-01-10 : 13:34:08
|
@webfred 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...@robvolkMy 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. |
|
|
|
|
|
|
|