MySQL Case in Select Statement with LIKE operator

Viewed 74627

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.

4 Answers
Related