March 22, 2011 at 7:13 pm
Any one can help me to parse city/state and zip
Below are sample data
City_State_Zip
Austin, TX 78746
Austin, Texas 78757
Round Rock, TX 78681
Round Rock, TX 78681-5005
Round Rock, TX 78681
I know i can use "Derived Column" Transformation, but i am not very good in expressions. Thanks.
March 23, 2011 at 4:12 am
Is it always the same format? If it is, you could use the following algorithm:
1. Use FINDSTRING and SUBSTRING to find the first comma and select the substring before that comma. That is the city.
2. Use FINDSTRING, REVERSE and SUBSTRING. Reverse the string, find the first space, get the substring before the first space and reverse it back. That is the zipcode.
3. Use a combination of the above the substract the state. You'll need a (fuzzy) lookup component to clean up the data.
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
March 24, 2011 at 9:32 pm
Thank you for your reply, It would be great if you can help me with expression i am not very good in expression.
March 25, 2011 at 12:39 am
Give it a try. You'll learn quicker and better if you try it out yourself. If you're stuck on something, post the expression you've already build here and we'll take a look at it.
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply