Using "LIKE" in "SELECT CASE" doesn't work - mysql

Question

I have a table of data. In that table there's a column called "TypeTag" that can be either an 11 digits number or 11 digits number "_" followed by 3 more digits. Meaning: 'XXXXXXXXXXX' OR 'XXXXXXXXXXX_XXX'.

I need the first part only - so I wrote a query:

SELECT 
    (CASE
          WHEN TypeTag LIKE '%_%' THEN LEFT(TypeTag, LENGTH(TypeTag) - 4) 
          ELSE TypeTag 
    END)
FROM `DailyReport` 
WHERE DATE>='2016-07-01'

The problem is that ALL of the values return without their last 4 characters, the case seems not to work (or maybe the LIKE doesn't work) because all of the records go into the first "WHEN" and not to "ELSE".

For example, if my original data set is:

56329856721
56329856722_502
56329856723
56329856724
56329856725_633
56329856726

Then the response I get is:

5632985
56329856722
5632985
5632985
56329856725
5632985

I'm using MySQL (SQLyog specifically). Does anyone know what's wrong with my query? or how to write the condition in a way that I'll get the needed result?

Thank you all!!

Answer 1

This is your CASE statement:

(CASE WHEN TypeTag LIKE '%_%' THEN LEFT(TypeTag, LENGTH(TypeTag) - 4) 
      ELSE TypeTag 
 END)

You have forgotten that _ is a wildcard (that matches exactly one character). So, the LIKE is checking that the value has at least one character.

You can escape it:

(CASE WHEN TypeTag LIKE '%\_%' THEN LEFT(TypeTag, LENGTH(TypeTag) - 4) 
      ELSE TypeTag 
 END)

Or, if you don't like \ as the escape character, you can define your own:

(CASE WHEN TypeTag LIKE '%!_%' ESCAPE '!' THEN LEFT(TypeTag, LENGTH(TypeTag) - 4) 
      ELSE TypeTag 
 END)

Or, you can simplify the entire logic by using SUBSTRING_INDEX():

SUBSTRING_INDEX(TypeTag, '_', 1)

That would be my preferred solution.