Insert "+" between each word in a SQL string - address check against USPS website
I need assistance with the following request.
I am working on a SSRS report and would like to take the address1, address2, city, state and Zip from my table and have it pre-populate the USPS web address for me to search for Zip codes.
Example, If I enter 1600 PENNSYLVANIA AVE NW WASHINGTON DC in the USPS search web page, I get this URL:
https://tools.usps.com/go/ZipLookupResultsAction!input.action?resultMode=1&companyName=&address1=1600+Pennsylvania+Ave+NW&address2=&city=washington&state=DC&urbanCode=&postalCode=&zip=
What I need to do is take my Address1 field and put a '+' between each word in the string. If I can get some assistance with doing this, I would greatly appreciate it. I hope this makes sense, if not, please let me know and I will try to clarify/elaborate more.
I am working on a SSRS report and would like to take the address1, address2, city, state and Zip from my table and have it pre-populate the USPS web address for me to search for Zip codes.
Example, If I enter 1600 PENNSYLVANIA AVE NW WASHINGTON DC in the USPS search web page, I get this URL:
https://tools.usps.com/go/ZipLookupResultsAction!input.action?resultMode=1&companyName=&address1=1600+Pennsylvania+Ave+NW&address2=&city=washington&state=DC&urbanCode=&postalCode=&zip=
What I need to do is take my Address1 field and put a '+' between each word in the string. If I can get some assistance with doing this, I would greatly appreciate it. I hope this makes sense, if not, please let me know and I will try to clarify/elaborate more.
+'+'+
ASKER
Address1 is one field - I need the string value of that broken out into distinct words with the '+' symbol between each word.
Example was the '1600 PENNSYLVANIA AVE NW'. I would need this formatted out to the following: '1600+PENNSYLVANIA+AVE+NW'
Example was the '1600 PENNSYLVANIA AVE NW'. I would need this formatted out to the following: '1600+PENNSYLVANIA+AVE+NW'
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
What you need is URL encoding. Changing spaces to + is only one aspect of this. Are you really addressing the USPS API from within SSRS?
Then take a look at this: http://capstonebi.blogspot.de/2010/04/url-encoding-in-reporting-services.html
Bye, Olaf.
Then take a look at this: http://capstonebi.blogspot.de/2010/04/url-encoding-in-reporting-services.html
Bye, Olaf.
ASKER
Thanks!