While I agree that it could return the rows, I also understand the system side. Null is nothing so it can not be compared to anything else, even another null. It is something like:
"I don't know whether it is like '%STU%' or not because it is nothing - I don't know what nothing is" Witold -----Original Message----- Jackson Sent: 30 January 2003 3:27 PM To: Multiple recipients of list ORACLE-L 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 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 Message----- > > From: Gary Jackson [SMTP:[EMAIL PROTECTED]] > > > > 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 > > ---- ---- -------- > > 1 1 STUFF > > 2 2 STUFF > > 3 3 > > 4 4 > > > > SQL> select * from tester2 where whatever not like '%STU%'; > > no rows selected > > > > > > My question is why does this not return the 3 & 4 columns? > > _________________________________________________________________ Protect your PC - get McAfee.com VirusScan Online http://clinic.mcafee.com/clinic/ibuy/campaign.asp?cid=3963 -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Gary Jackson INET: [EMAIL PROTECTED] Fat City Network Services -- 858-538-5051 http://www.fatcity.com San Diego, California -- Mailing list and web hosting services --------------------------------------------------------------------- To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Witold Iwaniec INET: [EMAIL PROTECTED] Fat City Network Services -- 858-538-5051 http://www.fatcity.com San Diego, California -- Mailing list and web hosting services --------------------------------------------------------------------- To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).