Solved

SQL String extraction with Case Select

Posted on 2011-03-10
4
272 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
4 Comments
 
LVL 32

Accepted Solution

by:
Ephraim Wangoya 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

What is a Denial of Service (DoS)?

A DoS is a malicious attempt to prevent the normal operation of a computer system. You may frequently see the terms 'DDoS' (Distributed Denial of Service) and 'DoS' used interchangeably, but there are some subtle differences.

Question has a verified solution.

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

If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
In this article I will describe the Copy Database Wizard 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.
In this brief tutorial Pawel from AdRem Software explains how you can quickly find out which services are running on your network, or what are the IP addresses of servers responsible for each service. Software used is freeware NetCrunch Tools (https…
Visualize your data even better in Access queries. Given a date and a value, this lesson shows how to compare that value with the previous value, calculate the difference, and display a circle if the value is the same, an up triangle if it increased…

623 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