VARCHAR2: NULL value vs. empty string
Keith Sauvant
is there _any_ way (Server Parameter?) to make Oracle store empty
strings in Varchar2s without converting them to NULL?
Thanks in advance
Keith Sauvant
Frank
No. Oracle != MS SQL Server (And not ANSI compliant in this matter)
--
Regards, Frank van Bortel
NoName
> strings in Varchar2s without converting them to NULL?
Thanks, Oracle, that automatically convert empty strings into NULL values!!!
I've been working using a RDB which did differences between empty and
nulls... oh, what a mess... in testing, sorting, searching... a nightmare!
Just a curiosity, why do you need to distinguish NULLS from empties?
Daniel Morgan
This has nothing to do with ANSI compliance. Where did you ever get the
idea that it did?
I'd suggest you read page 38 of Tom Kyte's "Expert one-on-one Oracle."
--
Daniel Morgan
http://www.outreach.washington.edu/ext/certificates/oad/oad_crs.asp
http://www.outreach.washington.edu/ext/certificates/aoa/aoa_crs.asp
damo...@x.washington.edu
(replace 'x' with a 'u' to reply)
Daniel Morgan
Because the ANSI standard allows vendors the ability to implement the
standard any way they wish. Oracle chose one way, others chose another way.
Do keep in mind that Oracle has been around a very long time. And many
of Oracle's design decisions were made before there was an ANSI standard
for databases. So as long as Oracle's implementation met the standard
there was no reason to change.
Keep in mind the obligation is for you, the developer to learn the tool
you are using. Not for Oracle, or any other vendor to do is the
Microsoft way just because Mr. Gates has such cool haircuts and more
money than god.
VC
Frank is actually right that Oracle is _not_ SQL'92 or SQL'99 compliant
with respect to empty strings treatment.
1. In SQL'92 an empty string is _not_ null. Oracle have neglected to fix
this problem for close to eleven years.
2. Oracle themselves admit their non-compliance (which is made even more
confusing by CLOBs compliance):
<quote>
Note:
IS NULL Semantic Discrepancy
In the SQL 92 standard, a character string of length zero is distinct from a
null string.
For an initialized LOB of length 0, you should expect `IS NULL' to return
zero (FALSE), since it is the correct and standard compliant behavior. In
contrast, a VARCHAR2 of length 0 returns TRUE on 'IS NULL`.
</quote>
http://www.engin.umich.edu/caen/wls/software/oracle/appdev.901/a88879/adl07m
13.htm#126418
3. Whilst undoubtedly Mr. Kyte's book is great, it does say much about
empty strings vs. nulls on page 38.
Besides, the standard is a more authoritative source than the book.
Rgds.
"Daniel Morgan" <damo...@x.washington.edu> wrote in message
news:1071249923.405427@yasure...
Daniel Morgan
VC wrote:
> Hello Daniel,
>
> Frank is actually right that Oracle is _not_ SQL'92 or SQL'99 compliant
> with respect to empty strings treatment.
>
> 1. In SQL'92 an empty string is _not_ null. Oracle have neglected to fix
> this problem for close to eleven years.
Provide a reference to either SQL standard that supports what you said.
Because I've been involved with this for a long time, corresponded with
Joe Celko, and there is nothing about your statement that I believe to
be true.
Before you respond you might want to look at this:
http://developer.mimer.com/validator/comparison/upd_comparison_chart.tml
And this from Stanford University:
http://www-cs-students.stanford.edu/~wlam/compsci/sqlnulls
Note the statement: "A boolean comparison between two values involving a
NULL returns neither true nor false, but unknown in SQL's three-valued
logic"
This is not possible with null strings because '' does indeed equal ''.
Also note the reference '[3]' to that statement:
C. J. Date and Hugh Darwen A Guide to the SQL Standard. Fourth edition,
Addison-Wesley, Reading, Massachusetts, 1997. (ISBN 0-201-96426-0)
page 239.
If you can find a better person to discuss relational theory and the
ANSI standard than C. J. Date I'd like to know who it is.
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
"Daniel Morgan" <damo...@x.washington.edu> wrote in message
news:1071275618.761761@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
Chris
VC
"Chris" <cs123._...@telstra.com> wrote in message
news:2CxCb.50465$aT.2...@news-server.bigpond.net.au...
> Just out of interest sake, how do you index a zero length string?
>
>
From SQL'92 standard:
<quote>
1) If <character substring function> is specified, then:
a) Let C be the value of the <character value expression>, let
LC be the length of C, and let S be the value of the <start
position>.
b) If <string length> is specified, then let L be the value of
<string length> and let E be S+L. Otherwise, let E be the
larger of LC + 1 and S.
<... skipped ...>
e) Case:
i) If S is greater than LC or if E is less than 1, then the
result of the <character substring function> is a zero-
length string.
</quote>
The answer to your question is: zero-length string ( S=1, LC=0 -> '').
Rgds.
Daniel Gustafsson
> NoName wrote:
>
> Because the ANSI standard allows vendors the ability to implement the
> standard any way they wish. Oracle chose one way, others chose another way.
That is wrong.
> Do keep in mind that Oracle has been around a very long time. And many
> of Oracle's design decisions were made before there was an ANSI standard
> for databases. So as long as Oracle's implementation met the standard
> there was no reason to change.
It does not meet the standard in this matter,
and Oracle also say that they don't follow the standard.
From the standard compliance tables in the Oracle SQL Reference
manual.
"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)"
This is also a quote from the Oracle SQL Reference manual about NULLs.
"(Oracle currently treats a character value with a length of zero as
null. However, this may not continue to be true in future releases,
and Oracle recommends that you do not treat empty strings the same as
nulls.)"
Regards
Daniel Gustafsson, Mimer SQL Development
http://www.mimer.se
Frank
> Frank wrote:
>
>> Keith Sauvant wrote:
>>
>>> Hi Group,
>>>
>>> is there _any_ way (Server Parameter?) to make Oracle store empty
>>> strings in Varchar2s without converting them to NULL?
>>>
>>> Thanks in advance
>>> Keith Sauvant
>>>
>>
>> No. Oracle != MS SQL Server (And not ANSI compliant in this matter)
>
>
> This has nothing to do with ANSI compliance. Where did you ever get the
> idea that it did?
>
> I'd suggest you read page 38 of Tom Kyte's "Expert one-on-one Oracle."
>
Oracle is not MS SQL server (and [Oracle] is not ANSI compliant in
this matter).
The ANSI standard defines an empty string as defined (which is not
the same as undefined, or NULL). Oracle still regards an empty string
as undefined.
Indeed, the OP problem has nothing to do with ANSI compliance; I just
noted that Oracle wasn't ANSI compliant in this matter, which kind of
surprised me too, when I found out.
Page 38/39 are about NULLs and NULL comparison - not about empty strings,
whatever gave you that idea?
And VC has given enough links to prove otherwise, if not, check your
own; the first link refers to a table, composed based on
an article, which states:
<quote>
For E131: Oracle won't distinguish NULLs from blank strings.
</quote>
It's in the Core Feature section of the article: where it says
<quote>
This section shows the exceptional cases where at least one DBMS misses
a core SQL:1999 feature.
</quote>
Based on this, I'm even inclined to say, that the first link quotes it's
source wrongly.
Your second link does not involve strings, either. As for your
conclusion, that '' does indeed equal '',
try this - this is 9.2.0.4 on Linux:
SQL> select nvl('','Is Null') from dual;
NVL('',
-------
Is Null
SQL> select nvl(NULL,'Is Null') from dual;
NVL(NUL
-------
Is Null
SQL> select 1 from dual where '' IS NULL;
1
----------
1
SQL> select 1 from dual where '' = NULL;
no rows selected
Oracle regards '' as NULL. Even your conclusion is easily proven
wrong:
SQL> select 1 from dual where '' = '' ;
no rows selected
'' is NOT equal ''
QED, rmc, eod
Daniel Morgan
> See below:
>
> "Daniel Morgan" <damo...@x.washington.edu> wrote in message
> news:1071275618.761761@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.
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
> From the standard compliance tables in the Oracle SQL Reference
> manual.
> "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)"
And how, exactly, do you correlate this with the ANSI standard? NULL in
the standard is a lack of a value ... it is not a zero length string.
You are making an assumption. And an incorrect one.
> Daniel Gustafsson, Mimer SQL Development
> http://www.mimer.se
Daniel Morgan
First of all there is no such thing as an ANSI standard if one defines
"an" as meaning a singular entity. The ANSI standard is not one thing.
The standard consists of three separate compliance levels. All major
commercial RDBMS products are level 1 compliant. What you folks have
been posting is pure unadulterated nonsense as you have likely never
actually read the standard and are make assumptions based on marketing
hyperbole and an assuming that if it isn't done the Microsoft way it
most be wrong. The truth is that SQL Server is NOT ANSI compliant on
default installation. It achieves compliance ONLY by modifying its defaults.
In the ANSI standard NULL is defined as the lack of a value ... not as a
zero length string and this is not subject to debate. It is the
definition.
Does Oracle support zero length strings? Absolutely. That you don't know
it is evidence that you have not been keeping up with Oracle. As you
know in version 9i Oracle added full support for ANSI joins. It also
added support for zero length strings. And here's the proof.
-- here's the table
CREATE TABLE t (x sys.anyData);
-- here's the insert statement
INSERT INTO t
VALUES (sys.anyData.convertVarchar2(''));
COL typeName FORMAT a20
-- proof the row was stored
SELECT COUNT(*) FROM t;
-- proof the data type is VARCHAR2
SELECT t.x.gettypeName() typeName
FROM t t;
Now you have the proof.
If you want to retrieve that empty string you will need to either go to
http://tahiti.oracle.com and learn how to use the anyData data type or
take my class at the University of Washington. I have two open seats for
the Winter Quarter starting in January ;-)
Andy Hassall
wrote:
>VC wrote:
>
>> "Daniel Morgan" <damo...@x.washington.edu> wrote in message
>> news:1071275618.761761@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
5 FROM dual;
CASEWHEN''ISNO
--------------
Non-Compliant
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 (an...@andyh.co.uk) icq(5747695) (http://www.andyh.co.uk)
Space: disk usage analysis tool (http://www.andyhsoftware.co.uk/space)
Andy Hassall
wrote:
>In the ANSI standard NULL is defined as the lack of a value ... not as a
> zero length string and this is not subject to debate. It is the
>definition.
Which is what everyone has been agreed on from the start.
>Does Oracle support zero length strings? Absolutely. That you don't know
>it is evidence that you have not been keeping up with Oracle. As you
>know in version 9i Oracle added full support for ANSI joins. It also
>added support for zero length strings. And here's the proof.
>
>-- here's the table
>CREATE TABLE t (x sys.anyData);
That's a cop-out, and you know it. Oracle's native varying-length character
datatype, VARCHAR2, is non-compliant with regards to empty string storage,
effectively as a result of its implementation and storage. Whether this is much
of a problem is another debate entirely, since distinguishing empty string from
NULL would require at least one more bit of storage somewhere. But that's not
_this_ debate.
Trying to argue that ANYDATA is a compliant character datatype is specious;
it's an ADT that can itself be null, or can hold one of the Oracle datatypes.
It still can't store an empty string, since an empty string is a NULL VARCHAR2.
All you've done is put an extra layer of indirection in, hiding it inside
another type.
And try building an index on it. Or using any of the ANSI string functions on
it. ANYDATA is not a character datatype, it's a container object for other
datatypes.
>-- here's the insert statement
>INSERT INTO t
>VALUES (sys.anyData.convertVarchar2(''));
>
>COL typeName FORMAT a20
>
>-- proof the row was stored
>SELECT COUNT(*) FROM t;
>
>-- proof the data type is VARCHAR2
>SELECT t.x.gettypeName() typeName
>FROM t t;
>
>Now you have the proof.
Presumably you're not being serious? All you now have proof of is that you
have a container that is storing a VARCHAR2.
INSERT INTO t
VALUES (sys.anyData.convertVarchar2(NULL));
Now distinguish the two rows from each other. Which is back to the original
issue.
VC
The stuff is below:
"Daniel Morgan" <damo...@x.washington.edu> wrote in message
news:1071424787.771334@yasure...
> VC wrote:
> > 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 )
>
[DM]
> Last time I checked www.engin.umich.edu was not an Oracle web site. And
> the last time I checked was two minutes ago.
OK., here's the same information from the horse's mouth:
http://download-west.oracle.com/docs/cd/B10501_01/appdev.920/a96591/adl07mds
.htm#126418
<quote>
Note::
IS NULL Semantic Discrepancy
In the SQL 92 standard, a character string of length zero is distinct from a
null string.
For an initialized LOB of length 0, you should expect `IS NULL' to return
zero (FALSE), since it is the correct and standard compliant behavior. In
contrast, a VARCHAR2 of length 0 returns TRUE on 'IS NULL`.
</quote>
I must admit I'm quite disappointed by your obvious failure to conduct a
logical discussion and inability to admit that you'd been mistaken on the
subject of such trivial nature. Apparently, I was misled by your other,
more reasonable, postings.
This is my last message in the thread as anyone with a kindergarten level
logic grasp can easily read and understand the SQL'92 standard snippet I
posted earlier. Oracle's own admissin of non-compliance is really redundant
for the discussion. For Oracle fans, it may be interesting to know that
the very VARCHAR2 data type is non-standard -- SQL'92 does not define it.
Rgds.
VC
Van Messner
Between NULL and a Zero Length String?)
"In Oracle version 7.0, a zero length string is treated by Oracle as NULL.
It is documented however that this will change in future releases.
Starting in release 7.1.3 and on through 8.1.7, a string of zero length ('')
is not equivalent to a NULL.
Your application should use a NULL when the value is unknown.
The following statement appears in the 7.3.x and 8.0.x release notes, as
well as in the Getting to Know Oracle8i manual: A string of zero length ('')
is not equivalent to a NULL. According to the ANSI SQL 1992 Transitional
standard, a zero-lenth or empty string is not the same as NULL. The Oracle
server may comply fully with this aspect of the standard in the future,
therefore, it is recommended that applications ensure that empty strings
values and NULL are not treated equivalently.
And in the migration to 9i docs:
"A string of zero length ('') is not equivalent to a NULL value. According
to
the ANSI SQL 1992 Transitional standard, a zero-length or empty string is
not
the same as NULL. Ensure that applications do not treat empty strings and
NULL
values equivalently."
"Daniel Morgan" <damo...@x.washington.edu> wrote in message
news:1071424889.658804@yasure...
Daniel Gustafsson
> Daniel Gustafsson wrote:
>
> > From the standard compliance tables in the Oracle SQL Reference
> > manual.
> > "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)"
>
> And how, exactly, do you correlate this with the ANSI standard?
That is a table describing Oracle's compliance to the ANSI standard.
> NULL in
> the standard is a lack of a value ... it is not a zero length string.
I don't think the term "lack of a value" is a proper description of
NULL, but you are correct that NULL is not a zero length string.
> You are making an assumption. And an incorrect one.
I do not make an assumption here.
Regards
Tony
> Daniel Gustafsson wrote:
>
> > From the standard compliance tables in the Oracle SQL Reference
> > manual.
> > "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)"
>
> And how, exactly, do you correlate this with the ANSI standard? NULL in
> the standard is a lack of a value ... it is not a zero length string.
>
> You are making an assumption. And an incorrect one.
Daniel,
You do seem (unusually) to be somewhat confused. The relevant ANSI
standard text about the empty string ('') not being NULL, and Oracle's
admission of non-compliance on that point, have been quoted several
times. You state here that NULL is not a zero length string -
correct. OK, so how do you represent a zero length string in Oracle?
Answer: you have to use a NULL.
Nobody is arguing (as you seem to have believed) that Oracle's
treatment of NULLs and 3-valued logic is wrong. It is Oracle's
treatment (or lack of treatment) of the empty string that is "wrong",
or at least non-ANSI. Because of the way Oracle implements NULLs, it
has no way to distinguish between '' and NULL - both are represented
by a character count of zero.
If NULL is not a zero length string, then why does Oracle do this? :-
SQL> select 'abc'||null from dual;
'AB
---
abc
It should return NULL, should it not?
Daniel Gustafsson
>
> The standard consists of three separate compliance levels. All major
> commercial RDBMS products are level 1 compliant.
There are no compliance levels in the current SQL standard.
> What you folks have
> been posting is pure unadulterated nonsense as you have likely never
> actually read the standard and are make assumptions based on marketing
> hyperbole and an assuming that if it isn't done the Microsoft way it
> most be wrong.
This is a proof that you are not serious.
An instructor should not display such an astonishing ignorance.
(As an aside, this has nothing to do with Microsoft.)
> Does Oracle support zero length strings? Absolutely. That you don't know
> it is evidence that you have not been keeping up with Oracle. As you
> know in version 9i Oracle added full support for ANSI joins. It also
> added support for zero length strings. And here's the proof.
>
> -- here's the table
> CREATE TABLE t (x sys.anyData);
>
> -- here's the insert statement
> INSERT INTO t
> VALUES (sys.anyData.convertVarchar2(''));
>
> COL typeName FORMAT a20
>
> -- proof the row was stored
> SELECT COUNT(*) FROM t;
>
> -- proof the data type is VARCHAR2
> SELECT t.x.gettypeName() typeName
> FROM t t;
>
> Now you have the proof.
You must be joking? Otherwise it is another proof that you are confused.
Andy Hassal posted a good example, I can repeat it again:
SELECT CASE
WHEN '' IS NOT NULL THEN 'Core SQL-99 Compliant'
WHEN '' IS NULL THEN 'Not compliant'
END
FROM T;
(this statement is, by the way, a valid SQL-92 Intermediate Level statement)
Regards
Daniel Gustafsson
Mimer SQL Development
http://www.mimer.se/
NoName
> SQL> select 'abc'||null from dual;
>
> 'AB
> ---
> abc
>
> It should return NULL, should it not?
I can assure you that empty string <> NULL value.
According to Oracle 8.0 SQL Reference Manual, Chapter 3, paragraph about
"Concatenation Operator":
--- start ---
Although Oracle treats zero-length character strings as nulls, concatenating
a zero-length character string with another operand
always results in the other operand, so null can result only from the
concatenation of two null strings. However, this may not
continue to be true in future versions of Oracle. To concatenate an
expression that might be null, use the NVL function to
explicitly convert the expression to a zero-length string.
--- end ---
Regards
Andy Hassall
wrote:
>I can assure you that empty string <> NULL value.
>According to Oracle 8.0 SQL Reference Manual, Chapter 3, paragraph about
>"Concatenation Operator":
>--- start ---
>Although Oracle treats zero-length character strings as nulls, concatenating
>a zero-length character string with another operand
>always results in the other operand, so null can result only from the
>concatenation of two null strings. However, this may not
>continue to be true in future versions of Oracle. To concatenate an
>expression that might be null, use the NVL function to
>explicitly convert the expression to a zero-length string.
>--- end ---
If you read that more closely, you'll see that it says that no,
Oracle does not distinguish '' from NULL, and is just advising you to
code defensively for the time when Oracle changes the behaviour, as
per the several notes in the manual.
SQL> select case
2 when nvl(null, '') is not null then 'Empty string not null'
3 when nvl(null, '') is null then 'Empty string null' end
4 from dual;
CASEWHENNVL(NULL,'')ISNOTNULLT
--------------------------------
Empty string null
i.e. using NVL(null,'') does not make it not-null; the change has not
yet been made. In particular they are warning that the following:
SQL> select 'x'||'' x, 'x'||NULL x from dual;
X X
- -
x x
Will return the following if the change is made:
X X
- -
x
And that if you are considering NULL as an empty string, instead of
writing:
'x'||nullablefield
... you should be writing:
'x'||nvl(nullablefield, '')
Which will act the same before and after the change.
Galen Boyer
> In the ANSI standard NULL is defined as the lack of a value
> ... not as a zero length string and this is not subject to
> debate. It is the definition.
ORA> create table tstnull (fld varchar2(10));
Table created.
ORA> insert into tstnull values (null);
1 row created.
ORA> insert into tstnull values ('');
1 row created.
ORA> select count(*) from tstnull where fld is null;
COUNT(*)
----------
2
ORA> select count(*) from tstnull where fld = '';
COUNT(*)
----------
0
If your posted phrase is the definition, it looks like Oracle
isn't within compliance.
--
Galen Boyer
Frank
And that's another non-ANSI compliant behaviour...
Look it up: concatenation of a string with NULL yields NULL,
concatenation of a string with an other, empty string yields
the first string
Ken Denny
OK damnit.
Daniel Morgan <damo...@x.washington.edu> wrote in
news:1071425957.927085@yasure:
[snip old stuff]
> -- here's the table
> CREATE TABLE t (x sys.anyData);
>
> -- here's the insert statement
> INSERT INTO t
> VALUES (sys.anyData.convertVarchar2(''));
OK now change the value to: sys.anyData.convertVarchar2(NULL)
and tell me if it changes anything.
>
> COL typeName FORMAT a20
>
> -- proof the row was stored
> SELECT COUNT(*) FROM t;
So what if the row was stored. Does the data item have a "NOT NULL"
constraint?
>
> -- proof the data type is VARCHAR2
> SELECT t.x.gettypeName() typeName
> FROM t t;
>
> Now you have the proof.
>
> If you want to retrieve that empty string you will need to either go to
> http://tahiti.oracle.com and learn how to use the anyData data type or
> take my class at the University of Washington. I have two open seats
> for the Winter Quarter starting in January ;-)
Show us a sample SQL where substituting '' and NULL as a VARCHAR2 value
produces a different result.
--
Ken Denny
http://www.kendenny.com/
Tony
Ever noticed how once their position becomes indefensible, most
posters never respond with "OK, I was wrong, you are right"? Instead
they merely ignore the thread altogether...
Frank van Bortel
Which seems the best thing to do, with a zealot like Daniel.
--
A prosperous 2004,
Regards,
Frank van Bortel