Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 289
  • Last Modified:

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...
0
cheryl9063
Asked:
cheryl9063
  • 2
2 Solutions
 
Ephraim WangoyaCommented:
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
 
derekkrommCommented:
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

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now