An Empty String Is Not Null

Why Oracle persists in this ridiculously bad design decision is beyond me. An empty string is a STRING of length 0. NULL is not a string at all. Forget philosphical arguments, it doesn’t help in any way to fail to distinguish ” from null. Whoever decided this obviously never had to write any code to work with recordsets returned from the database. I end up putting a single space where I really just wanted an empty string.

Advertisements

8 Responses to “An Empty String Is Not Null”

  1. Alvaro Lozada Says:

    Hi Jhon, Oracle developers have had to manage this since 1977 (When SDL – the formerly Larry Ellison’s company- developed Oracle). But let’s forget about the software brand and think about the real aplication of a zero length string. Why do you think that concept is useful? In real world when you look to a blank paper sheet did you recognize a zero length string… it could be a bad example but i wonder if the concept -despite it is an ANSI definition- is adding weight and complexity to the design and development of solutions? What do you think? (Demo:
    In Excel into a new sheet
    1) Type in a1: =”” (i mean equal sign and empty quotation marks) hit ENTER key

    2) Type in b1 =”dude” &a1&”!” hit ENTER key
    3) Copy b1 formula to c1
    4) Same result! (Hey this is not a real business case, but can we figure out the implications of storing “some” data to mean “Nothing”)

  2. jopincar Says:

    The problem with your analogy is that spreadsheet cells are not strongly typed.

    If you ever work with a database that supports empty string, try out never allowing null varchar fields, use default ” instead. It’s much easier to work with than being stuck with all the wierdness of null (in)equality tests, etc. and the extra hoops it forces you to jump through for nothing. Because 99.9% of the time, “” and null are used interchangeably in varchar fields.

  3. Alvaro Lozada Says:

    Ok… here you got a point: “It’s much easier to work with…” Using another really ugly analogy it is easier to drive an automatic gears car than a manual gears one, but there is no “ridiculously bad design decision” in manufacturing or driving a manual gear car (F1 cars uses the both systems). From my point of view, a manual gear car driver has advantage over an automatic gears car driver, certainly the first one would drive an automatic gears car, but it is not sure for the second one to drive an manual gears car. Then the automatic gears driver claims “Manual gears cars really sucks”. I promise to give a more IT specific answer to the reason due the absence of zero length strings in Oracle, “lo bueno, lo malo y lo feo” i mean “the good, the bad and the ugly”.

  4. Alvaro Lozada Says:

    As i promised… take a look at : awlozada.blogspot.com/2008/08/good-bad-and-ugly-of-abscence-of-zero.html

  5. Alvaro Lozada Says:

    awlozada.blogspot.com/2008/08/good-bad-and-ugly-of-abscence-of-zero.html

  6. Marius Vaiciulis Says:

    saying that NULL and ” are identical is the same as if you would say that 1 is equal to 1.0 .in most cases that would be true if you read one as the value and nothing more, but in fact the same one would stand for 0.9, 0.8, 1.1, 1.2, i.e. 1.0 is more exact. the same aplies to NULL and empty string. NULL says that the value is unknown, i.e. it is not set, it might be any value, but at the moment it is not determined, while when it is said that the value is ” (empty string) then it is explicitly said that this string is empty, whlist NULL doesn’t even say that this string even does exist. for example… lets say you have a table person(id, name, second_name, surname), and all values in columns id, name, surname are set, while some columns in second_name are ”, some has value of ‘second name’, and some NULL. where is the difference? if you query table for persons whose second name equals ”, you query for persons that do not have a second name, while query for persons whose second_name value equals NULL is a query for persons whose second name is unknown. and these two queries are different in all ways. output usualy is not part of logic the data is storied it’s just what user gets. but if you want to differentiate persons you must understand the difference between NULL and ”.

  7. jopincar Says:

    Marius, I’m not sure who you’re responding to. While I think your analogy is a little off, I agree that null and empty string should be two separate values. I personally rarely choose to use a null varchar and in Oracle’s case, if they were only going to offer one, I would have made it empty string, not null. At this point, Oracle’s failure to provide empty string is inexcusable, regardless of the history behind it.

  8. sanvi Says:

    I agree with jopincar that NULL and empty string are not the same. It is an unfortunate implementation error on Oracle’s part to treat them as such.

    There is a logical difference between NULL and an empty string. In the same way as 1 is an integer of value 1 and 0 is an integer of value 0, ‘x’ is a string of length 1 and ” is a string of length 0. An empty string is logically different from an unknown string (NULL) in the same way as 0 is logically different from unknown integer (NULL). Everyone agrees that storing 0 in an oracle NUMBER(1,0) column as NULL would be a grave error.

    C. J. Date has a lot to say about this concept in his new book, SQL and Relational Theory: How to write Accurate SQL Code published by O’Reilly.


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: