I'm doing a query, using NOT LIKE, but not getting the results I would like.

WHERE myfile NOT LIKE 'VAR%'

I would like for it to return ALL records that don't begin with VAR, but it
excludes records with a NULL value. I'm really hoping that there is a
simple work around that does not involve adding an OR clause. The
application that creates the query makes it really tough to add ORs.

It will do

( clause and clause) or (clause and clause)

but won't do

clause and (clause or clause)

and there are a bunch of clauses.

Any suggestions would be appreciated.

--
Randy Harris
(tech at promail dot com)

Randy Harris wrote:
NULL is special because you cannot equate it to another value like you
have done. That is why NULL is either IS NULL or IS NOT NULL.
Fortunately in Oracle, NVL gives you the power to assign a temporary
value to NULL so that you can use it in =, != etc operators. Try,

WHERE NVL(myfile, 'FOO') NOT LIKE 'VAR%'

Regards
/Rauf