David Brossard David Brossard - 4 months ago 14
MySQL Question

In MySQL using LIKE inside a SELECT case

I am building a view as follows...

create view usersAndTickets as
select tickets.subject as 'Name',
'automatically imported ticket from former support web' as 'Description',
case tickets.department_id when 3 then 'Support' when 4 then 'Support' when 5 then 'Feature Request' when 2 then 'Sales' end as 'Type',
case tickets.priority_id when 1 then 'Low' when 2 then 'Normal' when 3 then 'Urgent' end as 'Severity',
case tickets.status_id when 1 then 'Under Review' when 2 then 'Closed' when 3 then 'Waiting on Customer' when 4 then 'New' when 7 then 'New' end as 'Status',
users.email as 'EmailOfUserAssignedTo'
from custom_fields_values, tickets, users where tickets.staff_id=users.id;


In the view, I use CASE WHEN ... specific values to replace numbers with text.

But I also have a case where I do not want to do a strict equality comparison but rather I would like to do sthg like

case when custom_fields_values.value like '%Bar%' then 'Acme Product #1' end as 'Product',
case when custom_fields_values.value like '%Foo%' then 'Acme Product #2' end as 'Product',


But of course, MySQL complains the 'Product' field already exists.

So how do we use CASE WHEN with matching functions (LIKE) rather than equality?

Answer

I think you just want multiple clauses in the case expression:

(case when custom_fields_values.value like '%Bar%' then 'Acme Product #1' 
      when custom_fields_values.value like '%Foo%' then 'Acme Product #2'
 end) as Product,
Comments