MySQL Case in Select Statement with LIKE operator

问题:

Is it possible to combine the CASE statement and the LIKE operator in a MySQL SELECT statement?

For Example, I am trying to query a database that stores data in a single column in either one of two formats (this is awful and hurts my head, but I cant change the data so it is what it is.). So sometimes the column numbers would have data like "6901xxxxxxxx" and sometimes it would have data like "91xxxxxxxxxxx".

What I would like to do is query the data like so -

SELECT
    CASE digits
      WHEN LIKE "6901%" THEN substr(digits,4)
      WHEN LIKE "91%" THEN substr(digits,2)
    END as "digits",
FROM raw

This obviously doesn't work but Im hoping its possible.

回答1:

Using the second form of CASE should work:

SELECT
  CASE
    WHEN digits LIKE '6901%' THEN substr(digits,4)
    WHEN digits LIKE '91%' THEN substr(digits,2)
  END as digits
FROM raw

Furthermore, you have a stray comma at the end of your SELECT.

回答2:

Try

SELECT
    CASE true
      WHEN digits LIKE "6901%" THEN substr(digits,4)
      WHEN digits LIKE "91%" THEN substr(digits,2)
    END as "digits",
FROM raw

回答3:

Perhaps use LEFT()?

SELECT
    CASE 
      WHEN LEFT(digits, 4) = '6901' THEN substr(digits,4)
      WHEN LEFT(digits, 2) = '91' THEN substr(digits,2)
    END 
FROM raw

Should be more performant than the LIKE.

回答4:

Assuming the number of digits is constant, you could use something like this:

SELECT IF(digits > 919999, MOD(digits, 100), MOD(digits, 10000) FROM raw

Add 0's to match your actual number of digits.