Removing parts of a string from a variable
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)
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, '')ASKER
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..
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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?
5220 michaux rd greensboro north... I want to pull greensboro out.. What code can I use to do that?
ASKER
Thanks!
You're welcome. Sorry I couldn't be more help.
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).