Address Column Help - 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

SQL Server 2008 Forums

SSIS and Import/Export (2008)

Address Column Help

Author

Topic

Sonu619
Posting Yak Master

202 Posts

Posted - 2011-03-03 : 21:27:04

Hi guys,

Need help, Below are sampl data that i have in my source data.

ADDRESS

215 EAST 5TH ST DULUTH MN 55805

6080 CANT ROAD DULUTH MN 55804
C/O KURT BARTELL WEICHERT REALTORS TWIN PORTS 11 E SUPEIOR ST #290 DULUTH, MN 55802

13657 ELKWOOD DR APPLE VALLEY MN 55124 *SEND ALL MAIL TO OWNER*

2516 N 28TH ST SUPERIOR WI 54880

2110 JEFFERSON ST DULUTH MN 55812

7333 GALLAGHER DRIVE EDINA MN 55435

PO BOX 3144 DULUTH MN 55803

2231 E 2ND ST DULUTH MN 55812

Note:- I want to parse City/State and zip

Total i have 7k records in my source table i would say 5% are bad data, i have no problem if those 5% come out bad. I am using for zip code expression in deried column

ZIP = RIGHT([ADDRESS],@[User::ZIP]) *In variable i am using value of ZIP = 5*

I am getting 96% good zip and 5% those have bad data getting value
But i need help to parse city and state.

Please help me out in expression to get CITY AD STATE from the address (sample data listed above). Thanks for Help.

X002548
Not Just a Number

15586 Posts

Posted - 2011-03-03 : 22:27:48

Is that T-SQL?

Look at REVERSE and CHARINDEX in Books online and delimite by space

Or create a User defined function with "rules" and parse it out that way

Do you have a state code table to validate parsing? Or a city Table?

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

Want to help yourself?

http://msdn.microsoft.com/en-us/library/ms130214.aspx

http://weblogs.sqlteam.com/brettk/

http://brettkaiser.blogspot.com/

Sonu619
Posting Yak Master

202 Posts

Posted - 2011-03-03 : 23:34:17

Thanks for prompt reply. I am using SSIS to transfer Flat file source to Company database and source file has all address together and my company database has address/city/state and zip different columns. I am not very good in expressions if you can help me it would be awesome. Thanks.

latch
Yak Posting Veteran

62 Posts

Posted - 2011-03-04 : 11:14:11

Hi Sonu619,

What i am thinking is that:
As you getting the address column from the flat file:

Create a ssis package, take a data flow task with flat file as source:
Now,take a derived column transformation:

define three new columns as with the following expressions:

zip code: REVERSE(SUBSTRING(REVERSE(Address),1,5))
state : REVERSE(SUBSTRING(REVERSE(Address),7,2))

city: REVERSE(SUBSTRING(REVERSE(Address),9,LEN(Address) - 8))

i am not hard coding anything here its just assumption that always zip is of length 5 and state of length 2 then 5+2+2(spaces)=9
and 9-1 =8 to get the last letter while subtracting from reverse of string.

Now,take a OLEDB destination and map the respective columns.

Thanks,
latch

Sonu619
Posting Yak Master

202 Posts

Posted - 2011-03-04 : 23:27:30

latch thanks for your help. I used your expression but giving little bit problem, for
zip = RIGHT(ADDRESS_11,@[User::zip])
state = REVERSE(SUBSTRING(REVERSE(ADDRESS_11),8,2))

but still i couldn't figure it out how i can get city from address. Thanks for your time. If you come up with expression for "City" Please update me. Thanks You.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources