Case using contains and value conversion


I have two columns:

1. primary assembly

2. secondary assembly.


A person has 1 primary assembly, but at least 1 secondary assembly.

I am trying to do something like

if primaryAssembly contains "ABC" or if secondaryAssembly contains "ABC" then 1


Here is the case I am trying but the resulting values are totally not right:



 when `PrimaryAssembly` like '%Allergy Immunology & Inflammation%' or `SecondaryAssemblies` like '%Allergy Immunology & Inflammation%' then 1
 when `PrimaryAssembly` like '%Behavioral Science and Health Services Research%' or `SecondaryAssemblies` like '%Behavioral Science and Health Services Research%' then 2
 when `PrimaryAssembly` like '%Clinical Problems%' or `SecondaryAssemblies` like '%Clinical Problems%' then 3


I am getting numbers all over the place. not a true conversion in value.


How can I can values in both columns, and then assign a resulting value?

then convert that vlaue to another value?


so if "ABC" leads to 1 then 1 should then be converted into "This has ABC".


I am trying to get a count of each type, whether in the primary or the secondary.