Home » SQL & PL/SQL » SQL & PL/SQL » NULL VS '' (Oracle 11g Release 2, Linux)
NULL VS '' [message #657012] Wed, 26 October 2016 03:03 Go to next message
anil_mk
Messages: 144
Registered: August 2006
Location: Bangalore, India
Senior Member

Hi All,

Does Oracle converts '' as NULL internally? As per below example, I can see '' is converted as NULL. Please suggest.

create table test_null(v1 CHAR(5));
INSERT INTO test_null SELECT NULL FROM DUAL CONNECT BY LEVEL<=10;      
INSERT INTO test_null SELECT 'LVL'||LEVEL FROM DUAL CONNECT BY LEVEL<=10; 
UPDATE test_null
   SET V1 =''
WHERE V1 IS NULL;
COMMIT;
SELECT * FROM test_null WHERE V1 ='';   
SELECT * FROM test_null WHERE V1 IS NULL;



/*
SQL> create table test_null(v1 CHAR(5));

Table created.

SQL> INSERT INTO test_null SELECT NULL FROM DUAL CONNECT BY LEVEL<=10;

10 rows created.

SQL> INSERT INTO test_null SELECT 'LVL'||LEVEL FROM DUAL CONNECT BY LEVEL<=10;

10 rows created.

SQL> UPDATE test_null
2 SET V1 =''
3 WHERE V1 IS NULL;

10 rows updated.

SQL> COMMIT;

Commit complete.

SQL> SELECT * FROM test_null WHERE V1 ='';

no rows selected
SQL> SELECT * FROM test_null WHERE V1 IS NULL;

V1
-----











10 rows selected.

SQL>
*/

Thanks
Re: NULL VS '' [message #657013 is a reply to message #657012] Wed, 26 October 2016 03:42 Go to previous messageGo to next message
John Watson
Messages: 7262
Registered: January 2010
Location: Global Village
Senior Member
Your test looks perfect. The empty strings are interpreted as NULLs.
Re: NULL VS '' [message #657014 is a reply to message #657012] Wed, 26 October 2016 03:47 Go to previous messageGo to next message
cookiemonster
Messages: 13014
Registered: September 2008
Location: Rainy Manchester
Senior Member
Oracle doesn't have a concept of empty string so it treats '' as null in almost all cases. The one exception is when assigning values to char variables:
SQL> declare

  a char(5);
  b char(5);
  c varchar2(5);
  d varchar2(5);

begin

  a := '';
  b := null;
  c := '';
  d := null;

  dbms_output.put_line('char_empty_string $'||a||'$');
  dbms_output.put_line('char_null $'||b||'$');
  dbms_output.put_line('varchar_empty_string $'||c||'$');
  dbms_output.put_line('varchar_null $'||d||'$');

end;  2    3    4    5    6    7    8    9   10   11   12   13   14   15   16   17   18   19   20
 21  /
char_empty_string $     $
char_null $$
varchar_empty_string $$
varchar_null $$

Moral of the story is - always use null rather than ''.
Re: NULL VS '' [message #657016 is a reply to message #657014] Wed, 26 October 2016 04:26 Go to previous messageGo to next message
anil_mk
Messages: 144
Registered: August 2006
Location: Bangalore, India
Senior Member

Thanks for clarifying with example

Thanks
Re: NULL VS '' [message #657036 is a reply to message #657016] Wed, 26 October 2016 10:13 Go to previous message
Michel Cadot
Messages: 65387
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Oracle knows empty string as different from NULL in CLOB only.

http://www.orafaq.com/forum/mv/msg/195213/627385/#msg_627385

Previous Topic: transaction id sql
Next Topic: Query : Need help on populating prev day data in current day if missing
Goto Forum:
  


Current Time: Wed Feb 21 17:57:27 CST 2018

Total time taken to generate the page: 0.14757 seconds