MySQL CASE working backwards

Question

I'm using CASE to clean up some state abbreviations, in a table, but it's working contrary to the logic. I selected the length alone to show that the length is being calculated correctly, so I think it's the CASE logic that's off

When I query...

SELECT billing_state,
       length(billing_state),
       CASE billing_state
         WHEN length(billing_state) > 2 THEN (select state_abbr from lkup_states where upper(state_name) = billing_state)
         WHEN length(billing_state) = 2 THEN upper(billing_state)
         ELSE 'UNKNOWN'
       END as billing_state_fixed           
  FROM accounts
+---------------+-----------------------+---------------------+
| billing_state | length(billing_state) | billing_state_fixed |
+---------------+-----------------------+---------------------+
| GA            |                     2 | NULL                |
| Alabama       |                     7 | ALABAMA             |
| MS            |                     2 | NULL                |
| FL            |                     2 | NULL                |
| NULL          |                  NULL | UNKNOWN             |
+---------------+-----------------------+---------------------+

However, when I enter this bizarro logic, it works.

SELECT billing_state,
       length(billing_state),
       CASE billing_state
         WHEN length(billing_state) = 2 THEN (select state_abbr from lkup_states where upper(state_name) = billing_state)
         WHEN length(billing_state) <> 2 THEN upper(billing_state)
         ELSE 'UNKNOWN'
       END as billing_state_fixed           
  FROM accounts

+---------------+-----------------------+---------------------+
| billing_state | length(billing_state) | billing_state_fixed |
+---------------+-----------------------+---------------------+
| GA            |                     2 | GA                  |
| Alabama       |                     7 | AL                  |
| MS            |                     2 | MS                  |
| FL            |                     2 | FL                  |
| NULL          |                  NULL | UNKNOWN             |
+---------------+-----------------------+---------------------+

Can anyone take a swing at this one?

I don't think you need billing_state in your CASE billing_state WHEN length(billing_state) expression. — LittleBobbyTables - Au Revoir
– LittleBobbyTables - Au Revoir, Commented Oct 15, 2012 at 20:04
It looks like you are using a hybrid form of the case expression. There are 2 types. One with a CASE <expression>, another with CASE WHEN <expression>. Try removing billing_state from CASE billing_state. — btiernay
– btiernay, Commented Oct 15, 2012 at 20:05

Dancrumb · Accepted Answer · 2012-10-15 20:04:04Z

1

Per the docs, your syntax isn't quite correct.

You've muddle CASE value WHEN compare_value and CASE WHEN expression.

What you probably want is:

SELECT billing_state,
       length(billing_state),
       CASE
         WHEN length(billing_state) > 2 THEN (select state_abbr from lkup_states where upper(state_name) = billing_state)
         WHEN length(billing_state) = 2 THEN upper(billing_state)
         ELSE 'UNKNOWN'
       END as billing_state_fixed           
  FROM accounts

answered Oct 15, 2012 at 20:04

Dancrumb

27.7k10 gold badges79 silver badges139 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

zapatify Over a year ago

Geez. Thanks, Dan. I looked at the docs, but my eyes went straight for 'CASE value WHEN' and totally ignored 'CASE WHEN'. Thanks again.

Dancrumb Over a year ago

@ScoresOracle no worries! sometimes you just need that second pair of eyes :D

Collectives™ on Stack Overflow

MySQL CASE working backwards

1 Answer 1

2 Comments

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Related