Re: the difference between NULL and ' ':an interseting result

VALLAMPATI, MAHESH (m0v5533_at_zeus.tamu.edu) wrote:

: Hello folks,
: An interesting fact about NULLS in Oracle.This is a sequel
: to Mr.David Moore's post about SQL blanks where he was wondering
: about the difference between '' and ' '.Of course we had concluded
: that '' was NULL and whereas ' ' was a blank space.Now i have done
: a small experiment whose results i want to report to the net.
: First create a table like this
: ----> Create Table temp (col1 char(1));
: Now Insert a null into this table like this
: ----> Insert into temp values(NULL);
: Now spool the contents of this table to a file called one.lst
: ----> spo one
: ----> select * from temp;
: ----> spo off
: Now delete this entry(row) from this table.
: delete from temp;
: Now insert a blank into the table like this
: ----> Insert into temp values(' ');
: Now spool the contents of this table to a file called two.lst
: ---->spo two.lst
: ---->select * from temp;
: ---->spo off
: Now we have two files one.lst and two.lst.
 

: One would conclude that if u use the diff command in unix
: on this two files it would be different But if u run the
: command
: $ diff one.lst two.lst
: the diff does not report any
: difference.Strange isn't it.
: I look forward to hearing from the net.
 

: HAPPY NEW YEAR AND THREE CHEERS TO ORACLE.
 
: Regards,
: Mahesh Vallampati
 

: ____________________________________________________________________
: Mahesh Vallampati,(HOME) | Mahesh Vallampati(Office) |
: 301 Ball St., # 1086, | 244,WERC,Texas A & M Univ. |
: College Station, Tx - 77840 . | College Station,Tx 77840 |
: Ph : ( 409 ) 268 1489 . | Ph :(409 ) 845 6189 |
: ____________________________________________________________________
: I PROMISE TO USE THE KEY ,THE | THY SHALL
: WHOLE KEY AND NOTHING BUT THE KEY | INHERIT AND
: SO HELP ME CODD | DISTRIBUTE
: ____________________________________________________________________

This isn't strange if you do this on a V7 server. SQL Language Reference Manual p. 2-22 :

CHAR Datatype The CHAR datatype specifies a fixed length character string.

               When you create a table witch a CHAR column, you can supply
               the column length in bytes. ORACLE subsequently ensures that
               all values stored in that column have this length. If you
               insert a value tha is shorter than the column length, ORACLE
               blank-pads the value to column length. If you insert a value
               that is too long for the column, ORACLE returns an error.

VARCHAR2 Datatype The VARCHAR2 datatype specifies a variable length character
               string. When you create a VARCHAR2 column, you can supply the
               maximum number of bytes of data that it can hold. ORACLE
               subsequently stores each value in the column exactly as you
               specify it, provided it does not exceed the column's maximum 
               length. If you try to insert a value that exceeds this length,
               ORACLE returns an error.

Somebody did it on Oracle6 vs Oracle7 and is willing to comment?

Happy New Year to the net
LM

--
Leo Mannhart
Planning Office
University of Zurich                                phone: ++41 1 257 23 34
Kuenstlergasse 15                                     fax: ++41 1 257 22 12
CH-8001 Zurich, Switzerland                         eMail: mannhart_at_zuv.unizh.ch