Re: confused on "where x=null"...

From: DA Morgan <damorgan_at_psoug.org>
Date: Mon, 05 Mar 2007 09:04:14 -0800
Message-ID: <1173114253.602318@bubbleator.drizzle.com>

Thorsten Kettner wrote:
> On 3 Mrz., 00:49, Mark Harrison <m..._at_pixar.com> wrote:

>> It also does not match any rows where x is null.
>> Likewise, "where x=''" will not match any string
>> column.

>
> Your question about x=NULL has already been answered. So this is just
> some additional info: Unlike in SQL standard empty strings are NULL in
> Oracle. And with every release Oracle warns us that this may change in
> some future release and one should prepare one's statements for this.
> That means in Oracle you would never write <where str1 = str2>, but
> <where str1 = str2 or (str1 is null and str2 is null)> if one of the
> strings can be empty. You would also never write <where x="">, but
> <where x='' or x is null> or something alike. In Oracle SQL you must
> be very careful with string comparision and string function results,
> for example length(emptystring) is NULL, not zero as one would expect.

Or you can write:

WHERE SYS_OP_MAP_NONNULL(str1) = SYS_OP_MAP_NONNULL(str2)

and solve the entire problem.

-- 
Daniel A. Morgan
University of Washington
damorgan_at_x.washington.edu
(replace x with u to respond)
Puget Sound Oracle Users Group
www.psoug.org

Received on Mon Mar 05 2007 - 11:04:14 CST