(case when targeting_region like '%targ=%' then 1 else 0 end) as target_flag
(case when ((targeting_region like '%targ=%') and (targeting_region not like '%targ=!')) then 1 else 0 end) as target_flag
(case when ((targeting_region like '%targ=%') and (targeting_region like '%targ=!%')) then 1 else 0 end) as target_flag
Do you want only the 1st row to be displayed when you select and not the 2nd and 3rd. The above should work in that case. The reason you get a 0 is because of the lack of % after '%targ=!' and also that you never had the 2 conditions in the data of the 1st row. if you want the 2nd and 3rd rows to be displayed along with the 1st then you do an OR with targeting_region like '%targ=%'.
The first statement that you have written does not work due to the data you have that all the 3 rows have '%targ=%' in them. Hence all the 3 would retrun a value of 1 in the case.
Regular expression should work for you.
CASE WHEN REGEXP_SIMILAR ('targ=EU, targ=!Eu.Fr', '!', 'c') = 1 THEN 0
END AS target