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
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.