1

I'm working on a CASE expression that I need to have in order to search for, and parse out, specific text values.

Sometimes the relevant column will contain a person's name, and sometimes it will contain a city AND a two digit state abbreviation. I need to search in the column and determine if it contains a city and state, and if it does then I need to parse out the two character state abbreviation. For example, the column will contain '[PHOENIX, AZ.]'.

I currently have:

 CASE
      WHEN RIGHT(RTRIM(cu.address),2) IN('AL','AK','AZ', etc...) 
      THEN RIGHT(RTRIM(cu.address),2)

The problem I'm running into, is that sometimes there's a '.' (period) either before or after the two digit state abbreviation.

Rather than write a bunch of different code that would account for every type of anomaly, I need something that will account for everything.

Improve this question
5
  • 2
    Please update your question to include sample data (in the form of insert statements) that represent the scenarios you are trying to parse. We also need the expected results from a successful query against that data.
    – Scott Hodgin - Retired
    May 16, 2019 at 20:27
  • Can we rely on the fact that an existing comma always represents an address that will have a state code after it? What comes after the state code?
    – Scott Hodgin - Retired
    May 16, 2019 at 20:35
  • The state code should always be last, with the exception of a period or a blank space. A comma isn't always present, but there should always at least be a space between the city and state
    – Mike Jones
    May 16, 2019 at 20:38
  • 2
    Is there a reason you can't simply use replace to eliminate the period before the rtrim? CASE WHEN RIGHT(RTRIM(replace(@Address,'.','')),2) IN('AL','AK','AZ') THEN RIGHT(RTRIM(replace(@Address,'.','')),2) end as addr
    – Scott Hodgin - Retired
    May 16, 2019 at 20:42
  • Splendid! For extra credit, how would I then parse out the city portion (PHOENIX)?
    – Mike Jones
    May 16, 2019 at 21:37

0

Reset to default

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy