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 |
|
jscot
Posting Yak Master
106 Posts |
 Posted - 2011-02-02 : 14:22:30
|
| Guys here I am with problem, My Source data is Address1 Address2LOS ANGLES CA 92115 LOS ANGLES CA 95664 LOS ANGLES CA 98665 LOS ANGLES CA 98996And I want to map Address1,Address2 with target fields City, State, ZipMy question is how I can Split City, State, Zip and enter into respective columns.Thanks for your help. |
|
|
dataguru1971
Master Smack Fu Yak Hacker
1464 Posts |
Posted - 2011-02-02 : 14:49:12
|
You can use substring, reverse, right, left and various string parsing functions to manipulate the data.Where does Address1 and Address2 split? is there two columns or just 1 column of source data?
Poor planning on your part does not constitute an emergency on my part. |
 |
|
|
jscot
Posting Yak Master
106 Posts |
Posted - 2011-02-02 : 16:10:55
|
| Thanks for your reply. There is two different columns in source. Is there any way you can provide me sample code. I really appreciate. |
|
|
|
dataguru1971
Master Smack Fu Yak Hacker
1464 Posts |
Posted - 2011-02-02 : 17:52:33
|
It would depend on where the 2 columns are split. Where does column1 end and column 2 start?Are all your zip codes 5 digits?Are all the STATEs 2 characters?Is this the full row of sample data? or is there street address info to parse as well?
Poor planning on your part does not constitute an emergency on my part. |
|
|
|
jscot
Posting Yak Master
106 Posts |
Posted - 2011-02-02 : 18:25:45
|
| ALL STATES 2 CHR, ALL ZIP 5 CHR,CITY VARRIES, CLIENT KNOW DATA ARE NOT GOOD,I AM TRYING TO MY BEST TO GET CITY/STATE AND ZIP. CITY/STATE/ZIP ALL ARE SEPERATE WITH "SPACE". |
|
|
|
dataguru1971
Master Smack Fu Yak Hacker
1464 Posts |
Posted - 2011-02-02 : 18:52:05
|
you have STILL not told me where column1 ends and column2 starts! Maybe this will get you started. You actually aren't "trying" anything yet..Declare @foo varchar(100) select @foo = 'LOS ANGLES CA 98996'Select CITY = LEFT(@foo,LEN(@foo)-8) ,[St] = REVERSE(SUBSTRING(REVERSE(@foo),7,2)) ,[Zip] = REVERSE(SUBSTRING(REVERSE(@foo),1,5))
Poor planning on your part does not constitute an emergency on my part. |
|
|
|
jscot
Posting Yak Master
106 Posts |
Posted - 2011-02-06 : 23:31:53
|
| Sorry to late reply, Address1 has (First two Columns) and next two columns are empty, Address2 has (First two columns empty and last two columns has data. Please let me know how i can accomplish this problem in SSIS. My whole project through SSIS. Thanks for your help. Please let me know if my answer is still not clear. Thanks. |
|
|
|
|
|
|
|
|
Address Split Help