Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

How not to exclude null from where not like condition?

In where clause for my query if I include condition in where clause such as

where 
      T2.Comments not like '%ABC%' 
  and T2.Comments not like '%xyz%'

it also filters out blanks/null comments. I do not want it to filter out null or blanks.

What is another way to filter out eg. ABC and xyz and also not exclude nulls/blanks?

rest of where caluse.. (after adding bottom two condition I do not get any nulls.

Where
    T1.OUT_NO = T2.OUT_NO 
AND T3.OUT_NO = T1.OUT_NO 
AND CAUSE_CAP.CAUSE_NO NOT IN (1,3,5,7,9) 
AND ("T1"."TIME_STAMP">=TO_DATE ('01-04-2013 00:00:00', 'DD-MM-YYYY HH24:MI:SS') AND "T1"."TIME_STAMP"<TO_DATE ('06-04-2013 23:59:59', 'DD-MM-YYYY HH24:MI:SS')) 
AND NOT (CAUSE_CAP.CAUSE_NO = 13 AND START_TABLE.TABLE_NO = 83)
AND T2.Comments not like '%ABC%'
AND T2.Comments not like '%XYZ%'
like image 518
Mowgli Avatar asked Apr 08 '13 14:04

Mowgli


2 Answers

just add NULL as a specific case. [edited to show how to generalise this approach to OPs more complex query]

Where
    T1.OUT_NO = T2.OUT_NO 
AND T3.OUT_NO = T1.OUT_NO 
AND CAUSE_CAP.CAUSE_NO NOT IN (1,3,5,7,9) 
AND ("T1"."TIME_STAMP">=TO_DATE ('01-04-2013 00:00:00', 'DD-MM-YYYY HH24:MI:SS') AND "T1"."TIME_STAMP"<TO_DATE ('06-04-2013 23:59:59', 'DD-MM-YYYY HH24:MI:SS')) 
AND NOT (CAUSE_CAP.CAUSE_NO = 13 AND START_TABLE.TABLE_NO = 83)
AND (T2.Comments IS NULL OR 
        (T2.Comments not like '%ABC%'
         AND T2.Comments not like '%XYZ%')
    )
like image 115
drquicksilver Avatar answered Sep 22 '22 07:09

drquicksilver


When you have a condition (column LIKE 'somecode') then all NULL values are excluded from the results. Only rows that have not null can result in the condition being true. So, I think you want:

where  
      (   (   T2.Comments not like '%ABC%' 
          and T2.Comments not like '%xyz%'
          )
      or  T2.Comments IS NULL 
      )

AND has higher precedence than OR so the inner parentheses are not needed, only added for clarity (as @horse_with_no_name's comment). The outer ones are not needed either if you have only this condition.

In other DBMS, you would also need or T2.Comments = '' but not in Oracle, because NULL and the empty string are the same thing for char columns.

like image 21
ypercubeᵀᴹ Avatar answered Sep 23 '22 07:09

ypercubeᵀᴹ