Help with regular expression where expression isn't 'clean' 1

[elsenorjose]

In my table I have a field called market which is a geographical location like Los Angeles, Miami, etc. The values are stored like this:

LOSANGELES-CA
LITTLEROCK-AR
LONGISLAND-NY

I am trying to CASE this field so that 'valid' markets are displayed but invalid values and missing values are labeled as such. This snippet of SQL is what I'm using to check for the '-' between city and state

select case when market regexp '-' then market
else 'invalid value'...

My problem is that the way our logs are processed, NULL values are converted to '-'. So...my code will treat those '-' as legitimate market IDs and display them. I want those to go into the 'missing values' bucket.

How would I code my query so that if there is nothing before or after a '-', then treat it as a missing value?

Thanks

 

[r937]

select 
   case when market = '-'      
        then 'missing value'
        when market regexp '-' 
        then market
        else 'invalid value'   end as market

r937.com | rudy.ca

 

[elsenorjose]

You rock dude. Thanks

 

[elsenorjose]

Maybe you can help me expand this a bit further.

After running this query, I notice that there are some 'invalid' values that I'm not taking into account, namely markets like this:

BAKERSFIELD-
BAKERSFIELD-C
BOSTON-MA%bfPIr
BOSTON-MAw0R?%0C3w:[%07[
BOSTON-MAwx+%b6+3%ab%c2w:%de%c6%5b%d8%fd%a5+%ab%d8%c7%5b
BOSTON-Mw%b8%90+
BOSTON-Mw+%c0%e3+3%ab%c2w:%de%c6%5b%d8%fdp

Using the code you provided, how could I modify it to use the 2 digit state abbreviation to evaluate if a market is valid? In other words, only a market with 2 and only 2 characters after the hyphen are to be treated as valid. Everything else is either missing or invalid.

Thanks again.

 

[r937]

select 
   case when market = '-'      
        then 'missing value'
        when market regexp [b]'-[[:alpha:]]{2}$'[/b]
        then market
        else 'invalid value'   end as market

r937.com | rudy.ca

 

[elsenorjose]

Thanks again!