| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Usenet -> c.d.o.misc -> Re: VARCHAR2: NULL value vs. empty string
On Sun, 14 Dec 2003 10:01:19 -0800, Daniel Morgan <damorgan_at_x.washington.edu>
wrote:
>VC wrote:
>
>> "Daniel Morgan" <damorgan_at_x.washington.edu> wrote in message
>> news:1071275618.761761_at_yasure...
>>
>>>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"?
Nowhere, that's the point. According to the standard, '' is not null.
>> 5.3 <literal>
>>
>> Specify a non-null value.
>>
>> <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".
Yes, which is the point being made. If the empty string "is _not_ the same as null", then the expression
'' IS NOT NULL ... is true in a compliant database.
Not entirely clear on the '>' in your statement:
" an empty string > ('') "
Is that just a '>' quote that crept in from the previous poster?
>Oracle treatment of NULLs is fully ANSI compliant.
I do not see how you reach that conclusion.
SQL> SELECT CASE
2 WHEN '' IS NOT NULL THEN 'ANSI Compliant' 3 WHEN '' IS NULL THEN 'Non-Compliant' 4 END
CASEWHEN''ISNO
How can you reconcile these two view with each other; first you appear to agree with the standard that '' is a non-null empty string, yet you claim Oracle complies with this despite the results 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 )
>
>Last time I checked www.engin.umich.edu was not an Oracle web site. And
>the last time I checked was two minutes ago.
If you looked then you'd see that was a copy of the Oracle documentation. Same statement can be found at:
http://download-uk.oracle.com/docs/cd/B10501_01/appdev.920/a96591/adl07mds.htm#126418
With a further statement from Oracle claiming non-compliance at:
http://download-uk.oracle.com/docs/cd/B10501_01/server.920/a96540/ap_standard_sql.htm#11376
"
Oracle partially supports these subfeatures:
E021-02, CHARACTER VARYING data type (Oracle does not distinguish a zero-length
VARCHAR string from NULL)
E021-03, Character literals (Oracle regards the zero-length literal '' as being
null)
"
The areas of non-compliance being those in the brackets.
>> 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.
Provide a reference to such a claim?
>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.
Then why do Oracle themselves claim non-compliance with regards to empty string handling?
How do you reconcile the result from Oracle above, with the statement that '' is distinct from NULL?
-- Andy Hassall (andy@andyh.co.uk) icq(5747695) (http://www.andyh.co.uk) Space: disk usage analysis tool (http://www.andyhsoftware.co.uk/space)Received on Sun Dec 14 2003 - 16:34:18 CST
 |
 |