Oracle empty string = null

In Oracle 8, there is no such thing as a zero-length string. Any zero-length string, either from a function call or the literal '', is treated as null.

Some of the consequences of this are counterintuitive. Here are some examples.

  • The expression X=" is never true, regardless of what X is. In particular:
  • The expression ''='' is not true.
  • If you store an empty string ('') and read it back, the result will be null.
  • The expression trim(' ') returns null.

Be careful of this when writing your queries, particularly if you are porting code between Oracle and another database.

Share This | Comments | Permalink | Trackback | Comments feed

17 comments

  • Dead Krolik says:

    It’s a really bad behavior :( In MySQL empty string is not equal to null.

  • [...] McElwee blogged about this feature in more detail on his blog, but the jist of this nugget of goodness is that if you attempt to [...]

  • jianwu_chen says:

    That really should be considered as a Oracle design bug. Oracle really is not that good in my opinion.

  • Bennett says:

    I am not at all an Oracle expert, but as a developer I have found Oracle databases to be annoying to work with. I think their selling point is the boring enterprise stuff like reliability, scalability, etc.

  • Michael says:

    I personally like the fact that Oracle treats empty strings as null. Its a real pain having to test if something is null OR an empty string to find out if there is a usuable value there or not. And if I want to select rows where certain fields have blank values, I know that I will get everything I need by testing if null.

  • bachew says:

    Programming without null is like Mathematics without zero.

  • John says:

    I agree, ORACLE storing empty strings as NULL was a horrible design blunder. In a relational database, NULL is used to represent datum whose value is “unknown”. If I insert an empty string into a database field, I have specified a value for that field — a zero-length string. If I fetch that string from the database, the only correct result is an empty string.

  • bachew says:

    Oops, correction:
    A string variable that can’t be set empty is like a number variable that can’t be set zero.

  • Todd says:

    bachew - I agree.
    Also, you came back 20 days later to fix that. Awesome.

  • (another) John says:

    @Michael: if you don’t want to test for both ” and null, and your application treats both as semantically the same, just declare the field ‘not null’

  • [...] the semantic distinction between a value and not a value. Add the fact that the empty string  doesn’t exist in Larry’s World to the unbelievably idiotic lack of a BOOLEAN column type and you begin to [...]

  • Eric says:

    I agree that this is a horrible design decision. Not just because it screws compatibility with most other databases but because there is a school of thought held by many (the likes of C.J Date, and Hugh Darwin among them) that believe NULL is a Bad Idea and that there are better ways of representing UNKNOWN and NOT APPLICABLE (both of which people tend to use NULL for, often indistinguishably). This makes it almost impossible to excecise this discipline in Oracle.

    As to the comment “Programming without null is like Mathematics without zero.” this is completely wrong-headed. A numeric 0 in a relational database is a 0 not a null. Tell me, what is x + y if x or y is NULL?

    NULL complicates EVERYTHING and turns the clean 2 value logic of relational theory into the much more complex 3 value logic and this is the source of many problems.

  • Robert Weber says:

    Heres my two sense. Porting from SQL Server to Oracle (don’t ask why). In Sql server had:

    select field1,field2 from table where field1 ”

    This returned no results in Oracle, even though the majority of those fields had values. There was one that was null, which killed the query.

    In Oracle I tried tiering it with

    select field1,field2
    from table
    where trim(field1) ” and field1 is not null

    which also died, no results. The solution was

    select field1,field2
    from table
    where trim(field1) is not null

    In my opinion, that is just poor db design. The majority of the rows had values, but oracle died on the one null. A large relational database should be able to handle a null value the same way it handles a regular value, but nulls always blow up on oracle.

  • cipek says:

    that i need

  • Kamal says:

    @Bennett: if you call reliability and scalability boring enterprise stuff I guess you never had valuable data on a db ;)
    @Robert: NULLs don’t blow up on Oracle, it didn’t die on one NULL, it just considers ” as NULL, and an equality operator on a NULL is always false, it’s like saying WHERE col = NULL. That will always be false.
    You can also try WHERE ” = ” OR NULL = NULL, you won’t have any results.

    Anyway… I can’t think of a proper use of ” instead of NULL. I know it’s non standard but can you supply an example where this differentiation would be useful?
    I guess most of its use comes from poorly sanitized input.

    Cheers

  • Bennett says:

    @Kamal, I do think reliability and scalability are important. But they’re also boring to me. I’m glad that some people are interested in them though.

    One of the more common uses of null is to indicate “unknown” as @Eric mentioned. label='large' means that the label is “large”, label='' means the label is blank, and label=null means that we don’t know. In Oracle you have to do this with an extra Boolean field.

  • Bennett says:

    And let’s not forget that in Oracle,

    The expression ''='' is not true.

    I would love to see anybody try to argue that this isn’t nonsense.

Post a comment

(required)
(required)
(some HTML allowed; for literal < and &, use &lt; and &amp;)
Close
E-mail It