I can't figure out why sometimes LIKE requires ANY and other times it requires ALL, and it's making me crazy. I feel like I should be able to use ANY in both conditions (I'm trying to select records following any of the regex expressions in parentheses).
For some reason, the first LIKE, with ANY, works just fine - it returns all records with dog chow, pedigree, or beneful.
The second LIKE, however, requires ALL. Otherwise it won't leave out records with treat, supplies or wet. But why? I feel like ANY is the appropriate form here.
where dsc_item like any ('%DOG CHOW%','%PEDIGREE%','%BENEFUL%')
and dsc_comm not like all ('%TREATS%','%SUPPLIES%', '%WET%')
LIKE ANY translates to
ORed condition, but
LIKE ALL to
where ( dsc_item like '%DOG CHOW%' OR dsc_item like '%PEDIGREE%','%BENEFUL%' ) and ( dsc_comm not like '%TREATS%' AND dsc_comm not like '%SUPPLIES%' AND dsc_comm not like '%WET%' )
If you replace the
OR it's like
col <> 1 OR col <> 2 which is true for every non-NULL row.