OraFAQ Forum: SQL & PL/SQL » Difference between null & Empty string

Home » SQL & PL/SQL » SQL & PL/SQL » Difference between null & Empty string (Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Difference between null & Empty string [message #600780]

Tue, 12 November 2013 08:07

saipradyumn
Messages: 419
Registered: October 2011
Location: Hyderabad

Senior Member

Hi All ,

Is there any difference between null & empty string (with out any value between two single quotes '') ?

Thanks
Revathi.T

Report message to a moderator

Re: Difference between null & Empty string [message #600782 is a reply to message #600780]

Tue, 12 November 2013 08:21

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

SQL> select count(*) from emp where job = '';

  COUNT(*)
----------
         0

SQL> select min(empno) from emp;

MIN(EMPNO)
----------
      7369

SQL> insert into emp (empno,job) values (1,'');

1 row created.

SQL> select count(*) from emp where job = '';

  COUNT(*)
----------
         0

SQL> select count(*) from emp where job is null;

  COUNT(*)
----------
         1

SQL>

Report message to a moderator

Re: Difference between null & Empty string [message #600797 is a reply to message #600782]

Tue, 12 November 2013 11:20

Bill B
Messages: 1971
Registered: December 2004

Senior Member

ORACLE DOES NOT HAVE THE CONCEPT OF EMPTY LIKE T-SQL DOES. it is null or not null only

Report message to a moderator

Re: Difference between null & Empty string [message #600798 is a reply to message #600797]

Tue, 12 November 2013 11:24

gazzag
Messages: 1118
Registered: November 2010
Location: Bedwas, UK

Senior Member

NULL

HTH
-g

Report message to a moderator

Re: Difference between null & Empty string [message #600808 is a reply to message #600780]

Tue, 12 November 2013 13:02

John Watson
Messages: 8931
Registered: January 2010
Location: Global Village

Senior Member

Never mind ISO definitions, Oracle is clear. From the docs
http://docs.oracle.com/cd/E16655_01/server.121/e17209/sql_elements005.htm#SQLRF30037

Quote:

Note:
Oracle Database currently treats a character value with a length of zero as null. However, this may not continue to be true in future releases, and Oracle recommends that you do not treat empty strings the same as nulls.

The late Lex de Haan did some excellent work on this, such as,,
http://www.oracle.com/technetwork/issue-archive/2005/05-jul/o45sql-097727.html

Report message to a moderator

Re: Difference between null & Empty string [message #600813 is a reply to message #600797]

Tue, 12 November 2013 15:04

Solomon Yakobson
Messages: 3273
Registered: January 2010
Location: Connecticut, USA

Senior Member

Bill B wrote on Tue, 12 November 2013 12:20

ORACLE DOES NOT HAVE THE CONCEPT OF EMPTY LIKE T-SQL DOES. it is null or not null only

Not entirely true. Oracle has a concept of empty string. Oracle, as John showed, currently treats empty string as NULL. But it also in some cases treats NULL as empty string. Such case is string concatenation. If, for example, you add two NUMBER operands, e.g. a + b where a is 5 and b is null - result is null. But if we concatenate a || b where a is 'ABC' and b is null, Oracle essentially treats NULL as empty string and result is 'ABC'. Concatenation Operator:

Quote:

Although Oracle treats zero-length character strings as nulls, concatenating a zero-length character string with another operand always results in the other operand, so null can result only from the concatenation of two null strings. However, this may not continue to be true in future versions of Oracle Database. To concatenate an expression that might be null, use the NVL function to explicitly convert the expression to a zero-length string.

SY.

[Updated on: Tue, 12 November 2013 15:05]

Report message to a moderator

Re: Difference between null & Empty string [message #600903 is a reply to message #600780]

Wed, 13 November 2013 12:16

John Watson
Messages: 8931
Registered: January 2010
Location: Global Village

Senior Member

Your topic got badly hijacked (I supposed I started it - sorry about that) so I've split off the irrelevant stuff.

Report message to a moderator

Previous Topic:	Maximum Date
Next Topic:	The Book Of Null

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Thu Apr 25 01:45:10 CDT 2024