MySQL Case in Select Statement with LIKE operator

mysql case
HurnsMobile picture HurnsMobile · Aug 26, 2010 · Viewed 65.9k times · Source

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.

Answer

Daniel Vandersluis picture Daniel Vandersluis · Aug 26, 2010

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.

Related questions

MySQL select statement with CASE or IF ELSEIF? Not sure how to get the result

I have a two tables. One has manufacturer information and includes the regions where they can sell. The other has their products for sale. We have to limit visibility of the product based on the regions. This is like Netflix …

Read More
Case statement in MySQL

I have a database table called 'tbl_transaction' with the following definition: id INT(11) Primary Key action_type ENUM('Expense', 'Income') action_heading VARCHAR (255) action_amount FLOAT I would like to generate two columns: Income Amt and Expense Amt. Is …

Read More
How do I use properly CASE..WHEN in MySQL

Here is a demo query, notice it is very simple, Fetches only where base_price is 0, And still, it chooses the condition 3: SELECT CASE course_enrollment_settings.base_price WHEN course_enrollment_settings.base_price = 0 THEN 1 WHEN course_enrollment_settings.…

Read More