Menu
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%')
208
ANY - Compares value to each value returned by the sub query. ALL - Compares value to every value returned by the sub query.
Allows case-sensitive matching of strings based on comparison with one or more patterns. The operation is similar to LIKE . If the input string matches any of the patterns, this returns the input string.
Using the LIKE operator, you can specify single or multiple conditions. This allows you to perform an action such as select, delete, and updating any columns or records that match the specified conditions. It is mainly paired with a where clause to set the conditions.
SQL WHERE with ANY, ALL ANY and ALL operate on subqueries that return multiple values. ANY returns true if any of the subquery values meet the condition. ALL returns true if all of the subquery values meet the condition.
LIKE ANY translates to ORed condition, but LIKE ALL to AND:
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 AND with OR it's like col <> 1 OR col <> 2 which is true for every non-NULL row.
125
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
