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
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.
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
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
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?
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?
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...
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
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?
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.
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>
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
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 ?
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
?
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 ?
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.
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 !)
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 ?
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
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;
'>' '>'
--- ---
>< ><
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....
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.
January 06, 2020 - 3:12 am UTC
:-(