Solved

SQL String extraction with Case Select

Posted on 2011-03-10
4
267 Views
Last Modified: 2012-06-22
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
Comment
Question by:cheryl9063
  • 2
4 Comments
 
LVL 32

Accepted Solution

by:
ewangoya earned 250 total points
ID: 35099583
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
 
LVL 1

Author Comment

by:cheryl9063
ID: 35099621
How do you get the second to the last word out of a string?
0
 
LVL 15

Assisted Solution

by:derekkromm
derekkromm earned 250 total points
ID: 35099705
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
 
LVL 1

Author Closing Comment

by:cheryl9063
ID: 35099746
Thanks!
0

Featured Post

DevOps Toolchain Recommendations

Read this Gartner Research Note and discover how your IT organization can automate and optimize DevOps processes using a toolchain architecture.

Question has a verified solution.

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

In this article I will describe the Backup & Restore method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
Along with being a a promotional video for my three-day Annielytics Dashboard Seminor, this Micro Tutorial is an intro to Google Analytics API data.
This video shows how to use Hyena, from SystemTools Software, to bulk import 100 user accounts from an external text file. View in 1080p for best video quality.

778 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