Solved

Removing parts of a string from a variable

Posted on 2011-03-10
6
315 Views
Last Modified: 2012-05-11
The code is below works great for removing a variable called state from my variable called location.. I populate the @state variable from the information passed in to the @location variable and use it to "extract" it from the variable so all I have left in street.. ( I also do the same for city and zip).. Here is the problem:

When @Location is populated it could be populated with NC or North Carolina or WV or West Virginia or WVirginia etc.. When I populate the @State variable it goes through an outside process that takes whatever of those values and "normalizes" them to a 2 letter state.. So North Carolina becomes NC and Westvirginia becomes WV.. So when I go to remove them from @Location via the code below it does not always work..Removing the Zip is no problem so I can do that first and then state is next in line.. If the last word in my string is NC or NorthCarolina or North Carolina or NCarolina HOW can I remove it? I guess I could look for the space before the last word but what if someone puts a comma in between the city and state and sometimes they don't? Suggestions?( BTW I can not change the outside process)
set @location = replace(@location, @State, '')

Open in new window

0
Comment
Question by:cheryl9063
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 3
6 Comments
 
LVL 34

Expert Comment

by:Paul MacDonald
ID: 35098468
This will be nigh impossible to do with any certainty.  That's why so many forms break this information out into separate fields, and why state/country selection is almost always done with a dropdown list.

That said, can't you "normalize" @location with the same routine you use to "normalize" @state?  If you only "normalize" the last 20 or so characters of the @location, you should avoid hitting streets with state names (though you may still "normalzie" some cities with state names).
0
 
LVL 1

Author Comment

by:cheryl9063
ID: 35098506
No.. These are the requirements.. I have no say on front end and no say on MelissaData.. I just need to strip the state out of the string..I may have to do three set statments possible? First remove zip, then and commas and then some kind of function that finds the last word in the string and removes it..
0
 
LVL 34

Accepted Solution

by:
Paul MacDonald earned 500 total points
ID: 35099248
That seems feasible, but if the user lives in "North Carolina", you'll strip the "Carolina" but leave the "North".  And you won't know you need to remove the "North" because the "North" may belong to the street name.  There's no fool-proof way to accomplish the task you've been given (unless the front-end people relent and give you more normalized data).
0
What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

 
LVL 1

Author Comment

by:cheryl9063
ID: 35099350
OK.. So lets think of it this way.. What code will  Parse from the end of the variable to the first space and give me that word.. For example.. If this is what I have left

5220 michaux rd greensboro north... I want to pull greensboro out.. What code can I use to do that?

0
 
LVL 1

Author Closing Comment

by:cheryl9063
ID: 35099494
Thanks!
0
 
LVL 34

Expert Comment

by:Paul MacDonald
ID: 35099526
You're welcome.  Sorry I couldn't be more help.
0

Featured Post

Containers and Docker for Everyone

Containers are an incredibly powerful technology that can provide you and/or your engineering team with huge productivity gains. Using containers, you can deploy, back up, replicate, and move apps and their dependencies quickly and easily.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

PL/SQL can be a very powerful tool for working directly with database tables. Being able to loop will allow you to perform more complex operations, but can be a little tricky to write correctly. This article will provide examples of basic loops alon…
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
Monitoring a network: how to monitor network services and why? Michael Kulchisky, MCSE, MCSA, MCP, VTSP, VSP, CCSP outlines the philosophy behind service monitoring and why a handshake validation is critical in network monitoring. Software utilized …
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…

688 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question