FWD: Oracle can't tell the difference between NULL and "" on update/insert

From: Mike Kienenberger (mkienen..laska.net)
Date: Mon Apr 05 2004 - 14:38:05 EDT

  • Next message: Andrus Adamchik: "Single Table Inheritance Implementation"

    Here are some additional facets to this problem. The company I work for has
    an in-house database framework that they use and has had to work around
    several of these issues.

    -Mike

    Begin forwarded message:

    Date: Mon, 05 Apr 2004 10:02:22 -0800
    From: Raymond Billings <rb..vea.COM>
    Subject: RE: Oracle can't tell the difference between NULL and "" on
    update/insert

    It doesn't just apply to updates and inserts. If you have a column
    (test_column) with nothing but spaces ' ', then this is not considered to
    be a null, but trim(test_column) is considered to be null.

    It also causes problems on a "<>" (not equal) search).

    If for example you have a table containing the following data.

        ANIMAL

        ID NAME
        1 'whale'
        2 'dog'
        3 ' '
        4 'goat'

    If you do a "select * from animal where trim(name)<>'goat'" The result will
    be:

        1 'whale'
        2 'dog'

    Which is not likely to be the result that you expected to get. You have to
    add another condition to your where clause to get it to work correctly.

        "select * from animal where trim(name)<>'goat' or trim(name) is null"

    This archive was generated by hypermail 2.0.0 : Mon Apr 05 2004 - 14:37:10 EDT