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

On Mar 5, 9:54 am, "Thorsten Kettner" <thorsten.kett..._at_web.de> 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.

Which standard was that, out of interest?

Each time it is argued that SQL should support more than one kind of empty, I am more relieved that Oracle has stuck to their guns all these years in keeping it simple. For instance it came up here: http://forums.oracle.com/forums/thread.jspa?threadID=456874

Also what Oracle say may change in a future release is the currently unused VARCHAR type, so you would not have to change existing code.

Agreed it's not part of OP's question, just curious. Received on Tue Mar 06 2007 - 18:03:04 CST