Re: VARCHAR2: NULL value vs. empty string

From: Daniel Morgan <damorgan_at_x.washington.edu>
Date: Sun, 14 Dec 2003 10:01:19 -0800
Message-ID: <1071424787.771334@yasure>

VC wrote:

> See below:
>
> "Daniel Morgan" <damorgan_at_x.washington.edu> wrote in message
> news:1071275618.761761_at_yasure...
>

>>Comments in-line
>>
>>Provide a reference to either SQL standard that supports what you said.

>
>
> Firstly, SQL'92:
> .........................
> 4.2 Character strings
> ...
>
> 4.2.1 Character strings and collating sequences
>
> A character string is a sequence of characters chosen from the
> same character repertoire. The character repertoire from which
> the characters of a particular string are chosen may be specified
> explicitly or implicitly. A character string has a length, which
> is the number of characters in the sequence. The length is 0 or a
> positive integer.

And where in this paragraph do you see the word "NULL"?

The ANSI standard is not something you can treat like a religious text where different people come up with different interpretations.

> ...
>
> 5 Lexical elements
> ...
>
> 5.3 <literal>
>
> Function
>
> Specify a non-null value.
>
> Format
>
> <literal> ::=
> <signed numeric literal>
> | <general literal>
>
> <unsigned literal> ::=
> <unsigned numeric literal>
> | <general literal>
>
> <general literal> ::=
> <character string literal>
> | <national character string literal>
> | <bit string literal>
> | <hex string literal>
> | <datetime literal>
> | <interval literal>
>
> <character string literal> ::=
> [ <introducer><character set specification> ]
> <quote> [ <character representation>... ] <quote>
> [ { <separator>... <quote> [ <character representation>... ]
> <quote> }... ]
> ---------------------------------------------------------------------
>
> Since '[ <character representation>... ] ' with length zero equals '' and
> is, therefore, a valid literal for a character string, an empty string
> ('') is _not_ the same as null (see 5.3 above).

And there you go ...

"a character string, an empty string > ('') is _not_ the same as null".

Oracle treatment of NULLs is fully ANSI compliant.

> Secondly, I see you've conveniently skipped Oracle's own admission of
> non-compliance:
> "In the SQL 92 standard, a character string of length zero is distinct from
> a
> null string.
> " (
> http://www.engin.umich.edu/caen/wls/software/oracle/appdev.901/a88879/adl07m
> 13.htm#126418 )

Last time I checked www.engin.umich.edu was not an Oracle web site. And the last time I checked was two minutes ago.

>
> Nice try.
>
>
> Thirdly, what has Microsoft got to do with my posting ??? Could please build
> a logical connection ? I am intrigued ...

Because this is the argument Microsoft used to make when marketing SQL Server and it is pure nonsense. Since then Microsoft has added a configuration switch to SQL Server to make it perform as does Oracle. It is not configured that way on default installation but it is there. And the reason is so that they can claim ANSI compliance.

> VC

I appreciate that you may not understand the ANSI standard. And likely like most people have never actually read it. But check out the links below my name and understand ... that I have. Oracle is 100% ANSI compliant at level 1 and I believe fully compliant at level 2 too though there may be a a few gaps at level 2.

-- 
Daniel Morgan
http://www.outreach.washington.edu/ext/certificates/oad/oad_crs.asp
http://www.outreach.washington.edu/ext/certificates/aoa/aoa_crs.asp
damorgan_at_x.washington.edu
(replace 'x' with a 'u' to reply)

Received on Sun Dec 14 2003 - 12:01:19 CST