January 30, 2009 at 12:38 pm
I have a column called PhysicalAddress that I need to parse into it's own columns so that I can import it into separate address fields. The data in the column is like this:
Some Foundation, 1234 Something Plaza, 10th Floor, New York NY 10020-1234
I've found some code to parse it if it's all delimited with the same character, but in this case it's not. I've also found some code to get the zip code, but I need to be able to get the City and State as well. Can anyone help me with this. The code to get the zip code is this:
SELECT RIGHT(PhysicalAddress, ISNULL(NULLIF(CHARINDEX(REVERSE(' ') , REVERSE(PhysicalAddress)), 0) + LEN(' ')-1, 0)) as ZipCode
FROM Contacts
I can't seem to get this modified correctly to get the next space. Any help would be greatly appreciated.
Thanks!
Wendy Schuman
January 30, 2009 at 12:52 pm
It might be hard for the City, zip and Address part, but here's a splitter you can use to get the first address parts:
CREATE FUNCTION dbo.fn_StringtoTable2
(
@String NVARCHAR(MAX) ,
@Delimiter CHAR(1)
)
RETURNS TABLE
AS RETURN(
--INSERT INTO @Results
SELECT SUBSTRING(@String+@Delimiter, n,
CHARINDEX(@Delimiter, @String+@Delimiter, n) - n) as String
FROM tally
WHERE n <= LEN(@String)
AND SUBSTRING(@Delimiter + @String,
n, 1) = @Delimiter )
GO
To use this function, you will need a tally table, just search tally on this side, and you will see plenty examples by Jeff Moden, 😉
Do this, and then post your results with the rest of the job to do.
Hope it helps,
Cheers,
J-F
January 30, 2009 at 1:06 pm
I was able to parse everything into its own column using the comma as the delimiter and I was also able to use the select I posted earlier to get the zip code but I still have the City, State and Zip in one column. The data now looks like this:
Col_1=Some Foundation
Col_2=1234 Something Plaza
Col_3=10th Floor
Col_4=New York NY 10020-1234
Col_5=10020-1234
One of the problems I have is that all of the PhysicalAddress data doesn't have the same number of commas so not all of the City, State and Zip data goes into Col_4. I was hoping that I could get the City, State and Zip into their own columns before I use the parsing script by using the Right, Reverse functions I posted earlier. Is there a better way?
Wendy Schuman
January 30, 2009 at 1:28 pm
Well, if you are saying the commas are not always equally disposed, it will be pretty hard to get a script to do this. It looks like some manual work needs to be done to alter the data first.
You at least need a base you can count on, if you can get the zip, that might help, but then you'll still be caught with the comma thing.
Cheers,
J-F
January 30, 2009 at 1:46 pm
I was afraid of that. Thanks for your help.
Wendy Schuman
January 30, 2009 at 2:23 pm
There are programs (like Satori Software's MailRoom Toolkit) that can parse out addresses for you, and verify their mailability at the same time.
There are also companies that can do that kind of thing for you, so you don't have to buy the software.
Either of those solutions is going to be better than handling it yourself, if there's any real volume to the data.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
January 30, 2009 at 5:09 pm
If you're good with VB.net, you can do this using the Script Component of SSIS.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply