Why Does Oracle 9I Treat an Empty String as Null

Why does Oracle 9i treat an empty string as NULL?

I believe the answer is that Oracle is very, very old.

Back in the olden days before there was a SQL standard, Oracle made the design decision that empty strings in VARCHAR/VARCHAR2 columns were NULL and that there was only one sense of NULL (there are relational theorists that would differentiate between data that has never been prompted for, data where the answer exists but is not known by the user, data where there is no answer, etc. all of which constitute some sense of NULL).

By the time that the SQL standard came around and agreed that NULL and the empty string were distinct entities, there were already Oracle users that had code that assumed the two were equivalent. So Oracle was basically left with the options of breaking existing code, violating the SQL standard, or introducing some sort of initialization parameter that would change the functionality of potentially large number of queries. Violating the SQL standard (IMHO) was the least disruptive of these three options.

Oracle has left open the possibility that the VARCHAR data type would change in a future release to adhere to the SQL standard (which is why everyone uses VARCHAR2 in Oracle since that data type's behavior is guaranteed to remain the same going forward).

Oracle:Difference between NULL and EMPTY string

This is one of the most annoying features of Oracle - not found in other DB products. You will have to put up with it, for all the other massive advantages of Oracle - and be prepared that the learning curve is not very quick.

To check for equality of nulls, the best approach is to write explicitly what you are doing, instead of using gimmicks. For example:

... where NAME = INPUT or (NAME IS NULL and INPUT IS NULL)

This will make it a lot easier for yourself, and for others after you, to debug, maintain, and modify the code, now and especially later. There are other solutions, too, but they may confuse others in the future; for example, this is something I wouldn't use (for several reasons):

... where NAME || 'z' = INPUT || 'z'

although it would obviously achieve the same result with less typing.

One more thing, in most cases you should NOT include in your results rows where you treat NULL as "equal" - the values are NULL for a reason, and in most cases if you make two NULL's equal, that is NOT the intended result.

null vs empty string in Oracle

This is because Oracle internally changes empty string to NULL values. Oracle simply won't let insert an empty string.

On the other hand, SQL Server would let you do what you are trying to achieve.

There are 2 workarounds here:

1. Use another column that states whether the 'description' field is valid or not
2. Use some dummy value for the 'description' field where you want it to store empty string. (i.e. set the field to be 'stackoverflowrocks' assuming your real data will never encounter such a description value)

Both are, of course, stupid workarounds :)

Oracle Turns into NULL

As said in the comments, nothing you can do about it. Oracle treats an empty string no different from NULL and will store NULL into a varchar2 column when you supply it with an empty string.

What you can do if you really want to have something in there, store a single space instead.

empty string in oracle

This is a weird anachronism in Oracle (using default settings). Oracle does, indeed, treat an empty string as NULL. This includes in comparisons, so:

where mycolumn = ''

is the same as:

where mycolumn = NULL

And this never returns true (NULL <> NULL).

My advice? Get used to using NULL explicitly and writing:

where mycolumn is null

Insert empty string in Oracle

In Oracle, an empty string is equivalent to NULL.

In almost any other database, the two are different, but that is how Oracle defines NULL values for strings (by default).

This is explained in the documentation, along with this enticing note:

Note:

Oracle Database currently treats a character value with a length of
zero as null. However, this may not continue to be true in future
releases, and Oracle recommends that you do not treat empty strings
the same as nulls.

The highlighted portion is mine. I'm not sure how you are supposed to follow that recommendation. I think it means to use NULL explicitly, rather than '', when you intend NULL.

Note that in SQL, NULL represents an unknown value, not an empty value. There is a big difference between a string that has no characters (a perfectly valid string) and a NULL value which is unknown. In practice, NULL is often used for missing, but that is more of a convention than a definition.

Coalesce(null,'') gets null in Oracle and gets '' in SQL Server?

In Oracle document about NULL

Oracle Database treats a character value with a length of zero as null.

Oracle internally changes the empty string to NULL values. Oracle simply won't let insert an empty string.

select null from dual

the same as

select '' from dual

They are all return NULL.

So when you use select COALESCE (null,'') from dual it will translate to select COALESCE (null,null) from dual in Oracle.

sqlfiddle

Here is a link talk about this.

Why does Oracle 9i treat an empty string as NULL?

---

Related Topics