Re: VARCHAR2: NULL value vs. empty string

From: VC <boston103_at_hotmail.com>
Date: Sat, 13 Dec 2003 03:35:33 GMT
Message-ID: <9OvCb.379003$ao4.1266054@attbi_s51>

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.
...

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).

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 )

Nice try.

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

> Oracle has not admitted something that isn't true ... and it just isn't
> true. I'd suggest you stop believing everything you read from Microsoft
> Press.
>
> --

VC Received on Fri Dec 12 2003 - 21:35:33 CST