Empty Strings

A developer recently asked me to review the changes made to some code. Part of the change being made included the following in an query:

SELECT ... NVL(column, '')...

Yep, there was no space between those single quotes. I was surprised. “Surely this developer knows that in Oracle an empty string is NULL?” I asked myself as the developer in question was fairly senior and quite competent. However, on reflection, it’s one of those things that is not logically obvious.

So, for anyone reading this that does not know it already, Oracle treats an empty string as NULL and with that comes all the normal handling of NULLs.

In case there are any doubters, a smple test will confirm this:

SELECT NVL(NULL,'I am null')      AS result_1
,      NVL('','... and so am I!') AS result_2
FROM   dual

which gives us an output of:

RESULT_1  RESULT_2
--------- ----------------
I am null ... and so am I!

Furthermore, this behaviour is not limited to SQL, it applies to PL/SQL too as the following illustrates:

DECLARE
   l_temp   VARCHAR2(10) := '';
BEGIN
   IF (l_temp = '') THEN
      dbms_output.put_line ('String is empty');
   ELSE
      dbms_output.put_line ('String is not empty');
   END IF;
END;
/

which displays the following:

String is not empty

Bummer… don’t you just hate working with NULL!

When I first started developing in Oracle I was rather taken aback by this situation. Logically and conceptually an empty string and NULL are not the same. “Oracle is stupid” was my initial thought.

However after working with Oracle for a number of years now I can’t recall ever encountering a situation where this behaviour caused a problem. I have never been required to store an empty string and not have it stored as NULL. Application developers may beg to differ though. I can imagine that there may have been times whereby an application stored an empty string, then went to read the data back only to discover that it had been translated to NULL and this caused all sorts of problems. For example, a search type query that is looking for a empty string will definitely fail, e.g.:

SELECT *
FROM   some_table
WHERE  some_column = ''

This query will not return the rows where some_column is NULL. Instead, a test for NULL is required:

SELECT *
FROM   some_table
WHERE  some_column IS NULL

However, on closer consideration, what is the value in storing an empty string? Consider what would be required if an empty string was different to NULL. Visually the data would look the same and so would be considered in equal light by end users. As a result, queries would need to be written along the lines of:

SELECT *
FROM   some_table
WHERE  some_column IS NULL
OR     some_column = ''

Yuck! Queries would be a nightmare to maintain and manage. Given the difficulty in separating out empty string and NULL, bugs would arise in code and once that happens then data quality would suffer… and if there’s one thing that people demand of their database systems it’s accurate data!

Furthermore, consider the simple NOT NULL constraint. If an empty string was different to NULL then an effective way of bypassing a NOT NULL constraint would be to store an empty string… which is exactly the type of thinking that leads to data problems. Constraints are there for a reason and so coming up with ways to try to bypass them is not overly helpful and ends up doing serious harm to the data. To prevent the storing of empty strings so as to enforce the intention that the column holds a real value then CHECK constraints such as the following would abound within the database:

CHECK (some_column <> '')

Keep in mind that the column would still require a NOT NULL constraint as well since NULL would pass the above check constraint.

So, while I would agree that Oracle’s inability to handle empty strings and to treat them as NULL is not logically correct, I would argue that it is quite pragmatic and helps with maintaining data accuracy.

Advertisement