I have a function that takes the last word (the state) out of my address variable @Location and populates a variable called @State.. what a need is a case select that does this
If @State ='Carolina'
Then get the second to the last word from @location and concatenate it with @State
This is the same for Mexico, Jersey, York, Daokato etc..
IF @State = Virginia or VA then LOOK for the second to the last word in @Location and if 'West" is found then concatenate otherwise leave it...