Empty String vs NULL

July 29, 2011


How weird is the language you are programming on? User survey on stackoverflow is the second most voted thread. Somewhere on page 2 we encounter the following SQL code snippet:

create table wtf (key number primary key, animal varchar2(10));
insert into wtf values (1,'dog');
insert into wtf values (2,'');
insert into wtf values (3,'cat');
select * from wtf where animal 'cat';

I don’t agree with the sentiment of that post. How exactly returning an animal (or any other thing for that matter) named with empty string of characters would make user happy? For all practical purposes empty string of data is useless and equating it with NULL just reduces complexity.

Advertisements

5 Responses to “Empty String vs NULL”


  1. try this and explain me how it makes sense

    select
    length(replace(to_char(‘x’),’x’)),
    length(replace(to_clob(‘x’),’x’))
    from dual

    since Oracle7 doc, it is mentioned that ” should not be treated as null as it may change in a next release. It probably never will 😉

  2. Gary Says:

    My pet peeve is the way an empty string gets treated as null for TRANSLATE so you end up with stuff like TRANSLATE(col,’A1234567890′,’A’) as TRANSLATE(col,’1234567890′,”) returns NULL. But replace(col,’1′,null) works fine.

    zero-length character arrays make sense in some situations and forcibly treating them as null makes some things clunky.


  3. I agree: while empty strings are meaningless as names of things, they are legitimate programming language objects, and can appear to serve temporary purpose during program execution. Poor example on stackoverflow. On the other hand, if animal list contained a beast named “aaa”, it would be returned to the user; although, the user might suspect data entry error (or sabotage) — the same thing as with empty string.

    Laurent: what is clob, “Chained Letters Odd Breed”? This infestation of string datatypes (CHAR, VARCHAR(2!), CLOB, NCLOB) doesn’t make any sense.


  4. Sigh. Some folks have a middle initial that is in fact the empty string. There is an information difference between a string value that is definitively zero length and the value NULL and all its ramifications. Bob equated the two because he was newly using C, where a null pointer is effectively the same as the empty string, not because of analysis of relational values.

    • Ben Says:

      “Some folks have a middle initial that is in fact the empty string.”

      No, those folks don’t have a middle initial.


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

%d bloggers like this: