Akdora’s Blog

Programming, Oracle, Life, Fun

” is equal to NULL? January 29, 2008

Filed under: Oracle — Akdora @ 2:23 pm
Tags: , , ,
Connected to Oracle Database 10g Enterprise Edition Release 10.2.0.1.0

Connected as extoperSQL> set serveroutput on

SQL>

SQL> DECLARE

  2       test VARCHAR2(20) := '';

  3  BEGIN

  4       IF test IS NULL

  5       THEN

  6            dbms_output.put_line(''''' is equal to NULL');

  7       END IF;

  8 

  9       IF test = ''

 10       THEN

 11            dbms_output.put_line(''''' is equal to ''''');

 12       END IF;

 13  END;

 14  /

'' is equal to NULL

PL/SQL procedure successfully completed

SQL>
 

4 Responses to “” is equal to NULL?”

  1. APC Says:

    This is a very old fact about Oracle. It seems to annoy people who are new to the product. Old lags such as myself take a pragmatic view: what actually is the difference between a NULL and an empty string? People who claim there is a difference really don’t understand the concept of NULL. It is the absence of meaning.

    http://radiofreetooting.blogspot.com/search?q=+Yet+Another+NULL+Article

    Anyway, your message is wrong. Nothing is equal to NULL😉

    Cheers, APC

  2. Gary Says:

    “what actually is the difference between a NULL and an empty string?”
    If you consider a string as a sequence or array of characters, then an empty string can validly be a string that is known to consist of zero characters.
    This would be useful, for example, when you are using the TRANSLATE function to remove specific characters. Rather than specifying something like TRANSLATE(col,”,”), I have to supply a non-empty string TRANSLATE(col,’a’,’a’).
    Personally, I prefer that other databases are quite happy to differentiate between an empty string and a null/unknown string.


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s