CODEWITHSUNDEEP
sqlteradata
Adam
Adam

Reputation: 581

sql: like any vs like all

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%')

Upvotes: 6

Views: 64973

Answers (2)

David דודו Markovitz
David דודו Markovitz

Reputation: 44951

like any        similar to      = any  
like all        similar to      = all  
not like any    similar to      <> any  
not like all    similar to      <> all  

select      'My name is Inigo Montoya, you killed mhy father, prepare to die!'  as str

           ,case when str like any ('%Inigo%','%Donald%' ,'%Hillary%')      then 1 else 0 end -- 1
           ,case when str like any ('%Adam%' ,'%Donald%' ,'%Hillary%')      then 1 else 0 end -- 0
           ,case when str like all ('%Inigo%','%Montoya%','%father%')       then 1 else 0 end -- 1
           ,case when str like all ('%Inigo%','%Montoya%','%mother%')       then 1 else 0 end -- 0

           ,case when str not like any ('%Inigo%','%Montoya%','%mother%')   then 1 else 0 end -- 1
           ,case when str not like any ('%Inigo%','%Montoya%','%father%')   then 1 else 0 end -- 0
           ,case when str not like all ('%Adam%' ,'%Donald%' ,'%Hillary%')  then 1 else 0 end -- 1
           ,case when str not like all ('%Inigo%','%Donald%' ,'%Hillary%')  then 1 else 0 end -- 0
;

Upvotes: 2

dnoeth
dnoeth

Reputation: 60482

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.

Upvotes: 12

Related Questions