Skip to Main Content
  • Questions
  • Strings of Zero Length Not Equivalent To NULL

Breadcrumb

more

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's latest video and Chris's latest video from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.

Question and Answer

Tom Kyte

Thanks for the question, Saradha.

Asked: October 15, 2002 - 11:15 am UTC

Answered by: Tom Kyte - Last updated: January 06, 2020 - 3:12 am UTC

Category: Developer - Version: 9.2.0

Viewed 50K+ times! This question is

Whilst you are here, check out some content from the AskTom team: Please keep your foreign keys

You Asked

Tom,

As we are planning to upgrade to 9i version, I am reviewing the Oracle 9i migration documents and in one of the topic, I read
"Strings of Zero Length Not Equivalent To NULL".

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

I tested the following PL/SQL blocks in Oracle8i version and the
behavior looks different for CHAR variable and VARCHAR2 variable.

SQL> set serveroutput on

SQL> DECLARE
2 col1 VARCHAR2(1);
3 BEGIN
4 col1 := '';
5 DBMS_OUTPUT.PUT_LINE('Length :'||TO_CHAR(LENGTH(col1)));
6 IF (col1 IS NULL) THEN
7 dbms_output.put_line('Treated as NULL');
8 END IF;
8 IF (col1 = '') THEN
10 dbms_output.put_line('Treated as EMPTY STRING');
11 END IF;
12* END;
13 /

Length:
Treated as NULL

PL/SQL procedure successfully completed.

In the above example, col1 variable is declared as VARCHAR2 and the
empty string is treated as NULL.

SQL> DECLARE
2 col1 CHAR(1);
3 BEGIN
4 col1 := '';
5 DBMS_OUTPUT.PUT_LINE('Length :'||TO_CHAR(LENGTH(col1)));
6 IF (col1 IS NULL) THEN
7 dbms_output.put_line('Treated as NULL');
8 END IF;
8 IF (col1 = '') THEN
10 dbms_output.put_line('Treated as EMPTY STRING');
11 END IF;
12* END;
13 /

Length :1

PL/SQL procedure successfully completed.

In this example, col1 variable is declared as CHAR variable and
the empty string is neither treated as NULL nor treated as empty string. The length of the string is 1 in this case eventhough I have assigned an empty string to col1.

I have tested the above examples in 9i version also and the results
are same.

Can you please verify this difference as we have to modify some our PL/SQL programs according to this.

Thanks
Saradha

and we said...

A ZERO length varchar is treated as NULL.

'' is not treated as NULL.


'' when assigned to a char(1) becomes ' ' (char types are blank padded strings).

'' when assigned to a varchar2(1) becomes '' which is a zero length string and a zero length string is NULL in Oracle (it is no long '')



Now, I don't know what you mean by "we'll have to modify our code due to this" since this behaviour has been consistent for quite a while. Consider:

tkyte@ORA716.WORLD> select * from v$version;

BANNER
----------------------------------------------------------------
Oracle7 Server Release 7.1.6.2.0 - Production Release
PL/SQL Release 2.1.6.2.0 - Production
CORE Version 2.3.7.1.0 - Production (LSF Alpha)
TNS for SVR4: Version 2.1.6.0.0 - Production
NLSRTL Version 2.3.6.0.0 - Production

tkyte@ORA716.WORLD> set echo on
tkyte@ORA716.WORLD> @test
tkyte@ORA716.WORLD> DECLARE
2 col1 VARCHAR2(1);
3 BEGIN
4 col1 := '';
5 DBMS_OUTPUT.PUT_LINE('Length :'||TO_CHAR(LENGTH(col1)));
6 IF (col1 IS NULL) THEN
7 dbms_output.put_line('Treated as NULL');
8 END IF;
9 IF (col1 = '') THEN
10 dbms_output.put_line('Treated as EMPTY STRING');
11 END IF;
12 END;
13 /
Length :
Treated as NULL

PL/SQL procedure successfully completed.

tkyte@ORA716.WORLD>
tkyte@ORA716.WORLD> DECLARE
2 col1 CHAR(1);
3 BEGIN
4 col1 := '';
5 DBMS_OUTPUT.PUT_LINE('Length :'||TO_CHAR(LENGTH(col1)));
6 IF (col1 IS NULL) THEN
7 dbms_output.put_line('Treated as NULL');
8 END IF;
9 IF (col1 = '') THEN
10 dbms_output.put_line('Treated as EMPTY STRING');
11 END IF;
12 END;
13 /
Length :1

PL/SQL procedure successfully completed.





and you rated our response

  (32 ratings)

Is this answer out of date? If it is, please let us know via a Review

Reviews

October 15, 2002 - 1:06 pm UTC

Reviewer: Saradha from NJ, USA

Tom,

If Strings of Zero Length for VARCHAR2 variable is NULL
and Strings of Zero Length for CHAR variable is assigned with space values to the size of the variable, then what does the following convey:

"Strings of Zero Length Not Equivalent To NULL".

How do I store an empty string or How do I check a
variable in the program if it contains an empty string.

Thanks
Saradha

Tom Kyte

Followup  

October 15, 2002 - 3:13 pm UTC

well, as shown, a varchar2 will be NULL -- you would not be able to use a varchar2 variable and once assigned to a char -- it'll not be an "empty" string anymore.

Pls commnet on this wrt 9ir2

October 16, 2002 - 12:26 am UTC

Reviewer: A reader

SQL> set null NULL

SQL> ed
Wrote file afiedt.buf

  1*  select 1 ,length('') LEN  from dual where '' is null
SQL> /

         1        LEN
---------- ----------
         1 NULL

  1*  select 1 ,length('') LEN  from dual where to_char('') is null
SQL> /

         1        LEN
---------- ----------
         1 NULL

Q)
This does mean that empty string is null and the default data type of empty string is  varchar2 else if it where char then automatically its length would have been one. 

Tom Kyte

Followup  

October 16, 2002 - 10:02 am UTC

the '' is a being used as a varchar2, it is null.




nulls

October 16, 2002 - 12:00 pm UTC

Reviewer: Guy from Israel

Tom,

SQL> create table chr(x char(1));

Table created.

SQL> insert into chr values(null);

1 row created.

SQL> insert into chr values(null);

1 row created.

SQL> insert into chr values('G');

1 row created.

SQL> select * from chr where x<>'G';

no rows selected

SQL> select count(*) from chr where x is null;

  COUNT(*)
----------
         2

Why null is not different from 'G' ?

Thanks,
Guy
 

Tom Kyte

Followup  

October 16, 2002 - 1:11 pm UTC

tri valued logic.

it is UNKNOWN whether null = 'G' or null <> 'G' is true.

Why different from SQL ?

November 06, 2002 - 3:41 pm UTC

Reviewer: robert from PA

>> '' when assigned to a char(1) becomes ' ' (char types 
>> are blank padded strings).

Tom why is this behavior true in PL/SQL but NOT in SQL ?
that is, why did the designers of PL/SQL take a diff , (non-ANSI ?)  approach here ? 

SQL> desc  rc_test
Name Type        
---- ----------- 
A    CHAR(1)                             
B    CHAR(3)                              
C    VARCHAR2(3) 

SQL> insert into rc_test values ('', 'A', 'A');

1 row inserted

SQL> select * from rc_test where a is null;

A B   C  
- --- ---
  A   A            

thanks    

 

Tom Kyte

Followup  

November 06, 2002 - 3:50 pm UTC

don't know

Confused

November 06, 2002 - 4:14 pm UTC

Reviewer: Doug from CT, USA

The original poster refers to some 9i migration documentation that says "Strings of zero length are not equivalent to null", yet your explanation regarding char's and varchars indicates the opposite behavior for varchars all the way back to version 7. So is the migration documentation not really referring to anything new? or it is wrong, or what?

Tom Kyte

Followup  

November 06, 2002 - 4:55 pm UTC

the docs are telling you want ansi says vs what we do.

zero length CHAR field

November 06, 2002 - 5:16 pm UTC

Reviewer: Gary from Sydney, Aus

Regarding

>SQL> insert into rc_test values ('', 'A', 'A');
>1 row inserted
>SQL> select * from rc_test where a is null;
>A B   C  
>- --- ---
>  A   A            

In 8.1.7.4, if you do a DBMS_SQL.DESCRIBE of "select '', 'A', 'A' from dual", you'll see the first column is a CHAR field of length 0, whereas the other two are length 1.
I guess that it's being treated as a null CHAR(0) field at that stage, and then gets converted to a null CHAR(1) field for the insert. Just because the user is trying to insert it into a CHAR(1) field, doesn't mean it starts out being a CHAR(1).

 

different effect in SQL Server

November 06, 2002 - 9:43 pm UTC

Reviewer: A reader

In SQL Server, null is absolutely null and empty string is absolutely emtry string. '' is nevel equal to null

Does it mean SQL Server and Oracle treats null and empty string differently?

Tom Kyte

Followup  

November 07, 2002 - 7:27 am UTC

among zillions of other differences - in a word "yes"

Can we make Oracle return zero length string instead of NULL

November 14, 2002 - 11:16 am UTC

Reviewer: Arun Gupta from Harrisburg, PA USA

Tom
Is it possible somehow to make Oracle return a zero length string instead of NULL? We have lot of vb/asp code already written where programmers didn't check for NULL. It seems that vb/asp doesn't handle NULL. It crashes if we assign NULL values to variables and try to do some string operation. We have two options:
a) Modify vb/asp code (difficult, lot of code to change).
b) Make Oracle return zero length string instead of NULL (easy since only code within packages need to be changed).

We are on Oracle 8.1.7.

Thanks...

Tom Kyte

Followup  

November 14, 2002 - 7:29 pm UTC

there is no zero length string. you'd have to return a 1 character length string

there is no zero length string

September 11, 2003 - 1:11 pm UTC

Reviewer: A reader

Right. In a recent mysql to oracle intergration I found the NULL in mysql became oracle NULL (no problme there), and the "empty string" in mysql became ASCII code 0 in Oracle, which is a one char string (not null). ASCII code 0 represents the "NUL" character, and can be entered using oracle's CHR() function [ chr(0) ]. The entered value can be tested with ascii() function (i.e. select * from tab where ascii(col) = 0).

I don't see that as being correct.

January 26, 2004 - 12:41 pm UTC

Reviewer: A reader

justin@DEV> select ascii('') from dual;

ASCII('')
=================


The "empty string" is not showing to have an ascii value of '0'.

justin@DEV> select ascii(' ') from dual;

ASCII('')
=================
32

justin@DEV> select ascii('
2 ') from dual;

ASCII('')
=================
10


justin@DEV> select ascii(CHR(0)) from dual;

ASCII(CHR(0))
=================
0



Tom Kyte

Followup  

January 26, 2004 - 1:06 pm UTC

chr(0) is not an empty string that is why.

an empty string is not chr(0)


Only in C would '\0' be the emtpy string. SQL is not C.


agreed

January 26, 2004 - 1:24 pm UTC

Reviewer: Justin from PA

So...

Do you feel that the concept of an "empty string" and "null" are identical in meaning when it comes to Oracle?






Tom Kyte

Followup  

January 26, 2004 - 3:35 pm UTC

see the answer above -- depends on the use

Zero Length Strings in Varchar v Char

January 26, 2004 - 3:41 pm UTC

Reviewer: Adam Musch from Omaha, NE

No value will ever be equal to '' in Oracle, as '' in Oracle is NULL, and no value is ever equal to NULL, as a value can only "IS" or "IS NOT" NULL. (apologies for the pigdin English in that last bit).

A zero length string in a varchar column is null.
A zero length string in a char column is the width of the
column with spaces.

This is a portability issue with regards to Oracle vis-a-vis DB2, DB2 UDB, and SQL Server. A workaround for behavior consistent with those DBMS's is to use CHAR columns for nullable columns.

Is it kinda kludgy? Yes.
Will it result in wasted space? Yes.
Is that a consequence of application code portability? Yes.

An alternative would be to control DBMS access through views or triggers to eliminate this issue, as well as a host of others, such as join and data type conversion syntax.

Other shades using analytical...

January 27, 2004 - 5:13 am UTC

Reviewer: Franco from Denmark

Using analytical functions on imported data from another non-Oracle DB, probably you must consider also this:
-----------------------------------------------------------
</code> http://download-uk.oracle.com/docs/cd/A83908_02/NT816EE/DOC/index.htm <code>
-- (about RANK and DENSE_RANK functions) NULLs are treated like normal values. Also, for the purpose of rank computation, a NULL value is assumed to be equal to another NULL value.
-----------------------------------------------------------
Tom, how to rate your responses with more then 5 *****? :-)

Sorry for the wrong link...

January 27, 2004 - 5:21 am UTC

Reviewer: Franco from Denmark

SQL and pl/sql difference for chr(0)

April 22, 2005 - 2:46 am UTC

Reviewer: anindya mitra from Calcutta

SQL> exec dbms_output.put_line ('A'||chr(0)||'B');
A

PL/SQL procedure successfully completed.

SQL> select 'A'||chr(0)||'B' from dual;

'A'
---
A B

1 row selected.

SQL> select * from v$version;

BANNER
----------------------------------------------------------------
Oracle9i Enterprise Edition Release 9.2.0.5.0 - 64bit Production
PL/SQL Release 9.2.0.5.0 - Production
CORE    9.2.0.6.0       Production
TNS for IBM/AIX RISC System/6000: Version 9.2.0.5.0 - Production
NLSRTL Version 9.2.0.5.0 - Production

5 rows selected. 

Tom Kyte

Followup  

April 22, 2005 - 10:29 am UTC

chr(0) is the C null terminator, I would expect strange things with it, yes.

worked in 10g

April 22, 2005 - 7:51 pm UTC

Reviewer: Winston

SQL> set serveroutput on;
SQL> exec dbms_output.put_line ('A'||chr(0)||'B');
A B

PL/SQL procedure successfully completed.

SQL> select 'A'||chr(0)||'B' from dual;

'A'
---
A B

SQL> select * from v$version;

BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.1.0.3.0 - 64bi
PL/SQL Release 10.1.0.3.0 - Production
CORE    10.1.0.3.0      Production
TNS for Solaris: Version 10.1.0.3.0 - Production
NLSRTL Version 10.1.0.3.0 - Production

SQL>
 

Tom Kyte

Followup  

April 22, 2005 - 8:32 pm UTC

I would expect funny things to happen with chr(0) -- period.

'' is not longer ' ' with char(1)?

December 11, 2008 - 4:40 pm UTC

Reviewer: pinguman

SQL> create table t1 (c1 varchar2(1), c2 char(1));

Table created.

SQL> insert into t1 values (NULL, '');

1 row created.

SQL> insert into t1 values ('', NULL);

1 row created.

SQL> select dump(c1), dump(c2) from t1;

DUMP(C1)
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
DUMP(C2)
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
NULL
NULL

NULL
NULL


So has this been changed in 10gR2 or I am doing something wrong?

Thanks

Tom Kyte

Followup  

December 11, 2008 - 9:21 pm UTC

'' (quote||quote) has always been NULL in SQL.

pinguman

December 12, 2008 - 3:19 am UTC

Reviewer: Sokrates

In your very first answer on this thread you said:
"...
'' when assigned to a char(1) becomes ' ' (char types are blank padded strings).
...
"

That's really a bit confusing:

SQL> declare x char(1);
  2  begin
  3  x:='';
  4  if x is null then
  5  dbms_output.put_line('null');
  6  else
  7  dbms_output.put_line('not null'); 
  8  end if;
  9  end;
 10  /
not null


(as you said)

however

SQL> select 'null' from dual where cast('' as char(1)) is null;

'NUL
----
null



So, does this mean, that in SQL '' is always NULL whereas in PL/SQL '' might not be NULL ?
Tom Kyte

Followup  

December 12, 2008 - 7:36 am UTC

'' starts life in SQL as a varchar2 which is promoted to a char(n)

'' as a varchar2 is null.


and easily demonstrated

December 12, 2008 - 11:05 am UTC

Reviewer: Duke Ganote from Newtown, Ohio USA

declare x char(1);
begin
x:='';
if x is null then
dbms_output.put_line('null');
else
dbms_output.put_line('not null "'||x||'"');
end if;
end;
/
not null " "

PL/SQL procedure successfully completed.

Is this meaningless

December 14, 2008 - 7:14 am UTC

Reviewer: david

Hi

I have seen sQL statements as follows not sure if it's meaningful?

select *
from t1
where nvl(c1, '') = (select nvl(c2, '') from t2)

It is comparing everything except NULL, isnt this equivalent to

select *
from t1
where c1 = (select c2 from t2 where c2 is not null)
and c1 is not null

?
Tom Kyte

Followup  

December 29, 2008 - 8:08 am UTC

just

select * from t1 where c1 = (select c2 from t2)

would do it. The is not nulls are not needed.


but it seems the 'intent' was to make "null=null" in this case - to retrieve nulls (not that their intent was fulfilled - just that is what I think they were trying to do):

ops$tkyte%ORA9IR2> create table t1( c1 varchar2(1) );

Table created.

ops$tkyte%ORA9IR2> create table t2( c2 varchar2(1) );

Table created.

ops$tkyte%ORA9IR2>
ops$tkyte%ORA9IR2> insert into t1 values ( null );

1 row created.

ops$tkyte%ORA9IR2> insert into t2 values ( null );

1 row created.

ops$tkyte%ORA9IR2>
ops$tkyte%ORA9IR2> select *
  2  from t1
  3  where nvl(c1, '') = (select nvl(c2, '') from t2)
  4  /

no rows selected
<b>
ops$tkyte%ORA9IR2> select *
  2  from t1
  3  where decode( c1, (select c2 from t2), 1, 0 ) = 1
  4  /

C
-


1 row selected.
</b>


if so, decode could be used.

'' as a varchar2 is null - not always

February 22, 2012 - 4:01 am UTC

Reviewer: Sokrates


you wrote
...
'' as a varchar2 is null
...


doesn't seem to be always true:

sokrates@11.2 > declare
  2    type p is table of int index by varchar2(1);
  3    t p;
  4  begin
  5     t(null) := 1;
  6     dbms_output.put_line(t(null));
  7  end;
  8  /
declare
*
ERROR at line 1:
ORA-06502: PL/SQL: numeric or value error: NULL index table key value
ORA-06512: at line 5

BUT:
sokrates@11.2 > declare
  2    type p is table of int index by varchar2(1);
  3    t p;
  4  begin
  5     t('') := 1;
  6     dbms_output.put_line(t(''));
  7  end;
  8  /
1

PL/SQL procedure successfully completed.


Bug or feature ?
Tom Kyte

Followup  

February 23, 2012 - 12:47 am UTC

Interesting, that would be... a bug... thanks

Is anybody logged an bug.

March 16, 2012 - 1:17 am UTC

Reviewer: Ankur from India

Please let me know the bug id for the same NULL bug.
Tom Kyte

Followup  

March 16, 2012 - 8:37 am UTC

bug 13855494

it was a quiz question

March 16, 2012 - 9:27 am UTC

Reviewer: Sokrates

...
in Feuerstein's plsqlchallenge and they said it were a feature, so nice to hear it is a bug really ( and I correctly answered the question wrong !)
Tom Kyte

Followup  

March 16, 2012 - 9:49 am UTC

well, the bug could still be closed as "it is supposed to work that way", in which case - I'd be filing a documentation bug in order to get that clarified.

bug 13855494

March 16, 2012 - 9:54 am UTC

Reviewer: Sokrates

I don't manage to see this one on Oracle Support
Is it not visible to everyone ?
Tom Kyte

Followup  

March 16, 2012 - 11:14 am UTC

I don't set bugs I file to 'public', I wait for them to be worked on and fixed/resolved before that happens.

If you are encountering this issue, you can open an SR with support and reference the bug# (or even take over 'ownership' of it)

"...you can open an SR with support and reference the bug# (or even take over 'ownership' of it) "

March 16, 2012 - 11:37 am UTC

Reviewer: Sokrates

Thanks, I did so

insert woes

July 11, 2012 - 2:01 pm UTC

Reviewer: Kevin from Ann Arbor, Michigan

So a key question above seems to be ignored. Maybe this clarifies it:


drop table a40od620.exp2;
create table a40od620.exp2 (i1 number, c1 char(5), c2 varchar(10));
insert into a40od620.exp2 values(1, 'hello', 'there');
insert into a40od620.exp2 values(2, '', '');
insert into a40od620.exp2 values(3, NULL, NULL);
insert into a40od620.exp2 values(4, 'A', 'A');
update a40od620.exp2 set c1=replace(c1,'A',''), c2=replace(c2,'A','') where i1=4;
insert into a40od620.exp2 values(5, replace('A','A',''), replace('A','A',''));
select * from a40od620.exp2;
select * from a40od620.exp2 where c1 is null;
select * from a40od620.exp2 where c2 is null;
select i1, length(c1),length(c2) from a40od620.exp2;
quit;


I1 C1 C2
---------- ----- ----------
1 hello there
2
3
4
5


I1 C1 C2
---------- ----- ----------
2
3
5


I1 C1 C2
---------- ----- ----------
2
3
4
5


I1 LENGTH(C1) LENGTH(C2)
---------- ---------- ----------
1 5 5
2
3
4 5
5

So the ONLY way I have so far to insert an empty string into a char column is to insert a dummy string, 'A' and then use replace to set it back to an empty string ''. The insert of '' gets translated directly to a null, even for the char column, and the direct replace seems to first translate to the '' which is then translated into a null on insert.

Is there no way to insert an empty string into a char variable?? I am using 10.2.0.3 for this example if dbms version matters.

It might be nice to have another keyword like EMPTY_STRING.

Thx


Tom Kyte

Followup  

July 12, 2012 - 5:50 pm UTC

You didn't insert an empty string.

You created a string with five blanks in it. You could have just inserted ' ' to the same effect.


There is no such thing as an empty string in Oracle. the concept does not exist.



what you did was to

a) insert 'a', which really inserts 'a '
b) you then replaced 'a' with '' in the string, which turned 'a ' into ' ', but that 4 spaces put into a char 5 will become 5 spaces


no magic, just a string with five blanks in it.

insert woes

July 11, 2012 - 2:48 pm UTC

Reviewer: Kevin from Ann Arbor, Michigan

Ok, I think I see the problem. There really is no concept of an empty string for a char datatype. So what we want, end result, is all blanks. That can be achieved by inserting a single blank space, ' '. That gets padded to all blank spaces on insert as expected. It's still odd that other dbms' treat '' and NULL differently on insert. That seems to make more sense that Oracle's mapping of '' to NULL for a char field.

But I now have a better workaround than using replace...

char VS varchar2

July 17, 2012 - 3:31 am UTC

Reviewer: A reader

Hi Tom,

My test agaist char(1) and varchar2(1) seems is not same as what you mentioned.
When insert '' to both char(1) and varchar2(1), they are all NULL finally, any idea?

01:30:21 SQL> create table test(a char(1), b varchar2(1));

Table created.

01:30:23 SQL> insert into test values('','');

1 row created.

01:30:27 SQL> select '>'||a||'<','>'||b||'<' from test where a is null;

'>' '>'
--- ---
><  ><

01:30:30 SQL> select '>'||a||'<','>'||b||'<' from test where b is null;

'>' '>'
--- ---
><  ><

Tom Kyte

Followup  

July 17, 2012 - 8:32 am UTC

ops$tkyte%ORA11GR2> declare
  2          l_data char(1);
  3  begin
  4          l_data := '';
  5          if (l_data is null)
  6          then
  7                  dbms_output.put_line( 'is null' );
  8          else
  9                  dbms_output.put_line( 'is NOT null' );
 10          end if;
 11          for x in (select dump(l_data) x from dual)
 12          loop
 13                  dbms_output.put_line( x.x );
 14          end loop;
 15  end;
 16  /
is NOT null
Typ=96 Len=1: 32

PL/SQL procedure successfully completed.



we were talking about the behavior of PLSQL in this article.

Do CHAR(n) columns represent NULL as n spaces?

January 22, 2013 - 8:04 am UTC

Reviewer: Umberto Quaia

I thought it was that way, and that Oracle mapped
a column with all spaaces to NULL, but then I tried:


SQL> create table test ( c char(10) ) ;

Table created.

SQL> insert into test values ('') ;

1 row created.

SQL> select * from test where c is null ;

C
----------


SQL> select dump(c) from test ;

DUMP(C)
-------------------------------------------------------------

NULL

SQL> select length(c) from test ;

 LENGTH(C)
----------


SQL> truncate table test ;

Table truncated.

SQL> insert into test values (' ') ;

1 row created.

SQL> select * from test where c is null ;

no rows selected

SQL> select * from test where c = '  ' ;

C
----------


SQL> select * from test where c = '                    ' ;

C
----------


SQL> select dump(c) from test ;

DUMP(C)
-------------------------------------------------------------

Typ=96 Len=10: 32,32,32,32,32,32,32,32,32,32

SQL> select length(c) from test ;

 LENGTH(C)
----------
        10


So, the second case contains all spaces, but is not equiparated to NULL.

As usual, in char comparison, trailing spaces don't count.

So NULL is NOT represented using all spaces, all spaces seems to be equivalent to just one space.

Seems that NULL is treated as a special case.
Interesting....

Tom Kyte

Followup  

January 23, 2013 - 12:58 am UTC

null is not treated as a special case, null is just null.

nulls only take space for the null indicator, a flag that says "this space left intentionally empty".

all blanks (known, definitely present value) is definitely very different from null (unknown, missing value)

Do CHAR(n) columns represent NULL as n spaces?

January 22, 2013 - 8:04 am UTC

Reviewer: Umberto Quaia from ITALY

I thought it was that way, and that Oracle mapped
a column with all spaaces to NULL, but then I tried:


SQL> create table test ( c char(10) ) ;

Table created.

SQL> insert into test values ('') ;

1 row created.

SQL> select * from test where c is null ;

C
----------


SQL> select dump(c) from test ;

DUMP(C)
-------------------------------------------------------------

NULL

SQL> select length(c) from test ;

 LENGTH(C)
----------


SQL> truncate table test ;

Table truncated.

SQL> insert into test values (' ') ;

1 row created.

SQL> select * from test where c is null ;

no rows selected

SQL> select * from test where c = '  ' ;

C
----------


SQL> select * from test where c = '                    ' ;

C
----------


SQL> select dump(c) from test ;

DUMP(C)
-------------------------------------------------------------

Typ=96 Len=10: 32,32,32,32,32,32,32,32,32,32

SQL> select length(c) from test ;

 LENGTH(C)
----------
        10


So, the second case contains all spaces, but is not equiparated to NULL.

As usual, in char comparison, trailing spaces don't count.

So NULL is NOT represented using all spaces, all spaces seems to be equivalent to just one space.

Seems that NULL is treated as a special case.
Interesting....

using PL/SQL you have to assign NULL

February 20, 2013 - 9:06 am UTC

Reviewer: Umberto Quaia

 
SQL> declare
  2  col1 char(1);
  3  begin
  4     col1 := NULL ;
  5  dbms_output.put_line( 'Length: "'||to_char(length(col1))||'"' ) ;
  6  dbms_output.put_line( 'col1="'||col1||'"' ) ;
  7  end;
  8  /
Length: ""
col1=""
 
PL/SQL procedure successfully completed.
 
SQL> declare
  2  col1 char(1);
  3  begin
  4     col1 := '' ;
  5  dbms_output.put_line( 'Length: "'||to_char(length(col1))||'"' ) ;
  6  dbms_output.put_line( 'col1="'||col1||'"' ) ;
  7  end;
  8  /
Length: "1"
col1=" "
 
PL/SQL procedure successfully completed.
 
 
So, NULL is different from the empty string.
The empty string seems equivalent to the "all spaces" one.

bug 13855494

November 27, 2019 - 2:37 pm UTC

Reviewer: A reader

bug 13855494 seems to be back or still there (though declared as fixed)

sokrates@11.2 > declare
2 type p is table of int index by varchar2(1);
3 t p;
4 begin
5 t(null) := 1;
6 dbms_output.put_line(t(null));
7 end;
8 /
declare
*
ERROR at line 1:
ORA-06502: PL/SQL: numeric or value error: NULL index table key value
ORA-06512: at line 5

BUT:

sokrates@11.2 > declare
2 type p is table of int index by varchar2(1);
3 t p;
4 begin
5 t('') := 1;
6 dbms_output.put_line(t(''));
7 end;
8 /
1

PL/SQL procedure successfully completed.

Connor McDonald

Followup  

January 06, 2020 - 3:12 am UTC

:-(

More to Explore

DBMS_OUTPUT

More on PL/SQL routine DBMS_OUTPUT here