Re: NOT LIKE excludes NULLS

Randy Harris wrote:
> 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)

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 Received on Wed Feb 16 2005 - 18:56:47 CST