Case ... When with strings in mysql

Question

Hi I want to have some query like

Select 
 case column1
  when column2 like '%Test%' then column 3
  else 0
 end
FROM myTable

in column2 there are varchars stored. Can someone help me with this?

Answer 1

That doesn't make much sense. Why do you do a case on column1 but completely ignore that value afterwards? Did you just copy and (w)paste that into your query?

Anyways, this is what I would expect to work in any of the ordinary RDBMS':

case
  when colum2 like ...then
    3
  else
    0
  end as abc

However, the only thing I know of mySQL is that it usually does everything different than your average Joe RDMS would do them.

Answer 2

Do you mean something like...

Select column3 from myTable where column2 like '%Test%'  
union all  
select 0 from myTable where column2 not like '%Test%'

Note that the "not like" operator can be slow