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

Breadcrumb

Question and Answer

Connor McDonald

Thanks for the question, Saradha.

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

Last updated: January 06, 2020 - 3:12 am UTC

Version: 9.2.0

Viewed 100K+ times! This question is

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





Rating

  (32 ratings)

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

Comments

Saradha, October 15, 2002 - 1:06 pm UTC

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

A reader, October 16, 2002 - 12:26 am UTC

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
October 16, 2002 - 10:02 am UTC

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




nulls

Guy, October 16, 2002 - 12:00 pm UTC

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
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 ?

robert, November 06, 2002 - 3:41 pm UTC

>> '' 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
November 06, 2002 - 3:50 pm UTC

don't know

Confused

Doug, November 06, 2002 - 4:14 pm UTC

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
November 06, 2002 - 4:55 pm UTC

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

zero length CHAR field

Gary, November 06, 2002 - 5:16 pm UTC

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

A reader, November 06, 2002 - 9:43 pm UTC

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

Arun Gupta, November 14, 2002 - 11:16 am UTC

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

A reader, September 11, 2003 - 1:11 pm UTC

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.

A reader, January 26, 2004 - 12:41 pm UTC

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

Justin, January 26, 2004 - 1:24 pm UTC

So...

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






Tom Kyte
January 26, 2004 - 3:35 pm UTC

see the answer above -- depends on the use

Zero Length Strings in Varchar v Char

Adam Musch, January 26, 2004 - 3:41 pm UTC

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

Franco, January 27, 2004 - 5:13 am UTC

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

Franco, January 27, 2004 - 5:21 am UTC

SQL and pl/sql difference for chr(0)

anindya mitra, April 22, 2005 - 2:46 am UTC

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

Winston, April 22, 2005 - 7:51 pm UTC

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
April 22, 2005 - 8:32 pm UTC

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

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

pinguman, December 11, 2008 - 4:40 pm UTC

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
December 11, 2008 - 9:21 pm UTC

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

pinguman

Sokrates, December 12, 2008 - 3:19 am UTC

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

Duke Ganote, December 12, 2008 - 11:05 am UTC

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

david, December 14, 2008 - 7:14 am UTC

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

Sokrates, February 22, 2012 - 4:01 am UTC


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
February 23, 2012 - 12:47 am UTC

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

Is anybody logged an bug.

Ankur, March 16, 2012 - 1:17 am UTC

Please let me know the bug id for the same NULL bug.
Tom Kyte
March 16, 2012 - 8:37 am UTC

bug 13855494

it was a quiz question

Sokrates, March 16, 2012 - 9:27 am UTC

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

Sokrates, March 16, 2012 - 9:54 am UTC

I don't manage to see this one on Oracle Support
Is it not visible to everyone ?
Tom Kyte
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) "

Sokrates, March 16, 2012 - 11:37 am UTC

Thanks, I did so

insert woes

Kevin, July 11, 2012 - 2:01 pm UTC

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

Kevin, July 11, 2012 - 2:48 pm UTC

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

A reader, July 17, 2012 - 3:31 am UTC

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
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?

Umberto Quaia, January 22, 2013 - 8:04 am UTC

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
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?

Umberto Quaia, January 22, 2013 - 8:04 am UTC

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

Umberto Quaia, February 20, 2013 - 9:06 am UTC

 
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

A reader, November 27, 2019 - 2:37 pm UTC

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
January 06, 2020 - 3:12 am UTC

:-(

More to Explore

PL/SQL demos

Check out more PL/SQL tutorials on our LiveSQL tool.

PL/SQL docs

PL/SQL reference manual from the Oracle documentation library