Re: the difference between NULL and ' ':an
Date: 3 Jan 1994 10:56:18 GMT
Message-ID: <2g8tki$c4a_at_snlsu1.london.sinet.slb.com>
In article 525_at_rzu-news.unizh.ch, mannhart_at_zuv.unizh.ch () writes:
> 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.
>
> <deleted>
>
> 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
Problem is in diff not in Oracle. Take any two files where the only difference is empty line and line containing space. Diff will not notice this difference. To make experiment done by Mahesh more interesting and more educational instead of
select * from temp;
use
select '|'||col1||'|' from temp;
and check results.
Have fun.
Happy New Year to all netters.
Jacek
Jacek Steplewski
Schlumberger GeoQuest
P.O. Box 4754
Abu Dhabi
United Arab Emirates
phone (971-2) 333600
fax (971-2) 334575
tlx 23164 SCHADH EM
Received on Mon Jan 03 1994 - 11:56:18 CET