SQL String extraction with Case Select

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...
LVL 1
cheryl9063Asked:
Who is Participating?
 
Ephraim WangoyaConnect With a Mentor Commented:
something like this

case
  when (@State = 'Carolina' then
      case
          when charindex('West', Location, 1) > 0 then
              'West ' + @State
          else
              @State
       end
   else
      @State
end
0
 
cheryl9063Author Commented:
How do you get the second to the last word out of a string?
0
 
derekkrommConnect With a Mentor Commented:
declare @t varchar(100)
select @t = 'very large test'

select reverse(left(right(reverse(@t), len(@t) - charindex(' ', reverse(@t))), charindex(' ', right(reverse(@t), len(@t) - charindex(' ', reverse(@t)))) - 1))

0
 
cheryl9063Author Commented:
Thanks!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.