CODEWITHSUNDEEP
sqloracle-databaseoracle10g
Mowgli
Mowgli

Reputation: 3512

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

Upvotes: 7

Views: 19413

Answers (5)

Michael Nathan Bain
Michael Nathan Bain

Reputation: 21

nvl(t2.comments,' ') like '%xyz%'

Upvotes: 0

Duane Lorette
Duane Lorette

Reputation: 1

You might also try using a Case Statement, easy to read and gives you lots of flexibility to add additional conditions.

AND CASE WHEN t2.Comments IS NULL THEN 1
         WHEN T2.Comments like '%ABC%' THEN 0
         WHEN T2.Comments like '%xyz%' THEN 0
         ELSE 1 
         END = 1

Upvotes: 0

F.Kokert
F.Kokert

Reputation: 99

Putting it into a 'not like all' statement is making the intention clear and shortens the code for a better overview.

    and (T2.Comments not like all ('%ABC%','%xyz%') or T2.Comments is null)

Upvotes: -1

ypercubeᵀᴹ
ypercubeᵀᴹ

Reputation: 115600

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.

Upvotes: 4

drquicksilver
drquicksilver

Reputation: 1635

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

Upvotes: 12

Related Questions