PROTECTED]
CC: [EMAIL PROTECTED]
Subject: RE: comparing null values
Date: Thu, 30 Jan 2003 13:15:33 -0600
Gary,
Null has no value. It can't be like or not like anything. If you want to
see the null you'll need to add OR IS NULL to your Where clause.
Jerry Whittle
ASIFICS DBA
NCI Information Systems
31, 2003 12:42 AM
To: Multiple recipients of list ORACLE-L
Subject: comparing null values
Can anyone explain why it is that I seem unable to use 'like' and 'not
like'
on columns containing null values. (I am unable to find information
regarding this on MetaLink.)
For example
Can anyone explain why it is that I seem unable to use 'like' and 'not like'
on columns containing null values. (I am unable to find information
regarding this on MetaLink.)
For example:
SQL select * from tester2;
COL1 COL2 WHATEVER
11STUFF
22STUFF
33
4
It shouldn't, because comparison to NULL returns NULL, not TRUE or FALLS
If you want, you need:
select * from tester2 where whatever not like '%STU%' or whatever IS NULL;
Igor Neyman, OCP DBA
[EMAIL PROTECTED]
- Original Message -
To: Multiple recipients of list ORACLE-L [EMAIL
Title: RE: comparing null values
Gary,
Null has no value. It can't be like or not like anything. If you want to see the null you'll need to add OR IS NULL to your Where clause.
Jerry Whittle
ASIFICS DBA
NCI Information Systems Inc.
[EMAIL PROTECTED]
618-622-4145
-Original
Title: RE: comparing null values
Gary I think this goes back to the classic definition of NULL. NULL means unknown value. Therefore you can't say anything definite about the value. So it is not equal to anything or like anything, period. It is IS NULL from a SQL perspective.
HTH
Fred,
because a null value is not not like anything in the world. by
definition, a null is not comparable to anything. it is null. you cannot
check to see if something is comparable to null.
you can, however, change your query to say:
SQL select * from tester2 where nvl(whatever,'null') not
I believe that's expected behavior, arising from the 'null means we don't
know *what* the value is--it could be anything' nature of nulls. For all
the db knows, those nulls represent unknown values that *are* indeed like
'%STU%'. You should get the same result from select * from tester2 where
Why is this?? If I know that value X has no value, I certainly know that
value X is not like '%STU%' ?
From: Whittle Jerome Contr NCI [EMAIL PROTECTED]
To: [EMAIL PROTECTED]
CC: [EMAIL PROTECTED]
Subject: RE: comparing null values
Date: Thu, 30 Jan 2003 13:15:33 -0600
Gary,
Null has
Use:where whatever not like '%STU% or whatever is null
or use: where nvl(whatever ,'[NULL]') not like '%STU%
Regards,
Waleed
-Original Message-
Sent: Thursday, January 30, 2003 12:42 PM
To: Multiple recipients of list ORACLE-L
Can anyone explain why it is that I seem unable to
PROTECTED]
Subject: RE: comparing null values
Date: Thu, 30 Jan 2003 13:15:33 -0600
Gary,
Null has no value. It can't be like or not like anything. If you want to
see the null you'll need to add OR IS NULL to your Where clause.
Jerry Whittle
ASIFICS DBA
NCI Information Systems Inc.
[EMAIL PROTECTED
: RE: comparing null values
Date: Thu, 30 Jan 2003 13:15:33 -0600
Gary,
Null has no value. It can't be like or not like anything. If you want to
see the null you'll need to add OR IS NULL to your Where clause.
Jerry Whittle
ASIFICS DBA
NCI Information Systems Inc.
[EMAIL PROTECTED]
618-622-4145
Subject: comparing null values
Can anyone explain why it is that I seem unable to use 'like' and 'not
like'
on columns containing null values. (I am unable to find information
regarding this on MetaLink.)
For example:
SQL select * from tester2;
COL1 COL2 WHATEVER
13 matches
Mail list logo
