Changing address data - 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

Changing address data

Author

Topic

WJHamel
Aged Yak Warrior

651 Posts

Posted - 2011-10-14 : 11:05:14

I have a table which holds data for addresses. This data was imported from another customers excel sheets. The address info on their source held the entire street address in one field "address", (i.e., "12 W. Center Street". It was imported as such into our table in the "Street" column. I now need to break that data out into other columns in our table. for instance, the "12" needs to break out to the "Housenu" column in the same table, the "W" needs to break out to the "Quad" column in the same table, the "Center Street" can stay in Street, and "Apt 1" needs to break out to the "Housesu" column in the same table.

Are there suggestions as to how i can script this out?

thanks

James

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-10-14 : 11:11:12

is your address format consistent? ie always house no followed by Quad, Street etc?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

WJHamel
Aged Yak Warrior

651 Posts

Posted - 2011-10-14 : 11:15:12

I wish. No, it's very inconsistent.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-10-14 : 11:19:36

then no way to split it properly in one go. you might have to do it in sets depending on their format.

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

WJHamel
Aged Yak Warrior

651 Posts

Posted - 2011-10-14 : 11:27:11

wonderful. Well, for the sake of having a starting direction, let's assume that they were all consistent in their format (i may be able to influence that on my end). If that were so, is there a way to take this on?

Grifter
Constraint Violating Yak Guru

274 Posts

Posted - 2011-10-14 : 11:31:06

Can you give us an idea of the inconsistencies. for instance in this:

12 W. Center Street

and all other data is there a common theme in any of them, like quad always = W and always has a dot (.) after it?

G

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-10-14 : 11:35:17

yep. do like


SELECT PARSENAME(REPLACE(REPLACE(Street,'.',''),' ','.'),3) AS Housenu,
PARSENAME(REPLACE(REPLACE(Street,'.',''),' ','.'),2) AS Quad,
PARSENAME(REPLACE(REPLACE(Street,'.',''),' ','.'),1) AS Street
FROM YourTable

this will work for format like those posted before
------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

WJHamel
Aged Yak Warrior

651 Posts

Posted - 2011-10-14 : 11:40:22

Grifter:
I can group the data together in some semblence of commonalities, but the inconsistencies vary wildly. There can be entries which only state "Commons Street", others which might state "12 Commons St.", yet others that state "2 W Commons Street", and "2 w. Commons St." and so on. There is (was) no standard followed by the persons who originally input this data.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-10-14 : 11:42:43

so in such cases how do you want splitting to happen?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2011-10-14 : 11:44:26

The way to do this usually is to start with a format, use that to transform the file, find an address that doesn't match - put in code to handle that type, transform the file - continue untill all addresses are ok or enough to make you happy.

In your situation you can probably do updates for each part in turn - I would save the address in another table first.

update tbl
set houseno = left(addr,charindex(' ',addr)-1) ,
addr = right(addr,len(addr)-charindex(' ',addr))
where left(addr,charindex(' ',addr)-1) like '%[^0-9]%'

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

WJHamel
Aged Yak Warrior

651 Posts

Posted - 2011-10-14 : 12:06:45

Visakh: I'm still looking at that to see what can be done with what i have. Thanks

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-10-14 : 12:07:49

ok. np
lets us know if you gets a way out

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources