MySQL: List and like?

Question

I want to compare the value of a column with several list and print something else in each case. Its a list of car brands, like Chevy, Ford and so on. My logic should be like this:

if(mycolumn like carlist1, 'this', '')
if(mycolumn like carlist2, 'that', '')
if(mycolumn like carlist3, 'foobar', '')

mycolumn has a value like Chevy-1234, so it must be a like since the numbers will change. How could I do that in MySQL?

Thanks!

Answer 1

Your best bet is to store the car list as a table, with data like:

Chevy
Ford

and so on.

Then you can do what you want with a join:

select t.*,
       max(case when cl.brand = 'Chevy' then 'this' end) as Chevy,
       max(case when cl.brand = 'Ford' then 'this' end) as Ford,
       . . .
from t left outer join
     carlist cl
     on left(t.col, length(cl.brand)) = cl.brand
group by t.id

Otherwise, you have to deal with the number:

select (case when left(col, locate('-', col) - 1) = carlist1 then 'this' end),
       (case when left(col, locate('-', col) - 1) = carlist2 then 'that' end),
       (case when left(col, locate('-', col) - 1) = carlist3 then 'foobar' end)