Results 1 to 14 of 14
  1. #1
    Join Date
    Jun 2006
    Posts
    11

    Unanswered: Oracle empty string == NULL behavior in SQLServer 2k5?

    I'd like to have Oracle's empty string behavior in SQLServer 2k5. Oracle treats an empty string as NULL's.

    In PL/SQL can do:
    Code:
    SELECT * FROM TABLE WHERE TABLE.FIELD IS NULL
    ... and it'd return rows containing NULL's as well as empty strings.

    Can this be done? I couldn't find a setting for it.

    Thanx

    Peter

  2. #2
    Join Date
    Nov 2003
    Posts
    2,935
    Provided Answers: 12
    As far as I know, the only way to do this is
    Code:
    SELECT * FROM theTable WHERE field IS NULL or field = ''

  3. #3
    Join Date
    Nov 2002
    Posts
    272
    Or
    Code:
    SELECT * FROM theTable WHERE ISNULL(field, '') = ''

  4. #4
    Join Date
    Jun 2006
    Posts
    11
    Quote Originally Posted by shammat
    As far as I know, the only way to do this is
    Code:
    SELECT * FROM theTable WHERE field IS NULL or field = ''
    Yep, thanx but we got this far. We were hoping to avoid typing this. Maybe some system wide setting... but I guess we'll have to learn to do it the MS way.

  5. #5
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    It is less doing things the MS way and more not doing things the Oracle way. I didn't know you could do that in PL\SQL but it sure as 'ecky thump isn't part of the ANSI standard.

    Anyway - a lot of people just prevent nulls from their database in the first place.

    Code:
     
    CREATE TABLE MyTable
    (
    MyCol VARCHAR(10) DEFAULT '' NOT NULL
    )
    HTH
    Testimonial:
    pootle flump
    ur codings are working excelent.

  6. #6
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Absolutley. Oracle's treatment of empty strings as nulls is not standard, and is a bad idea.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  7. #7
    Join Date
    Nov 2003
    Posts
    2,935
    Provided Answers: 12
    Quote Originally Posted by blindman
    Absolutley. Oracle's treatment of empty strings as nulls is not standard, and is a bad idea.
    This issue has been discussed a lot
    I think it's perfectly OK.

  8. #8
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    The war in Iraq has been discussed a lot too. I guess that makes it OK as well?

    A zero-length string is distinctly different that a null value. Anyone who equates the two does not understand the concept of a null value. A null value is "unknown" or "undetermined". A zero-length string has a known value. How can you possibly say that a known value is equal to some value that is not known? You can't. Hence, Oracle's implementation is flawed.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  9. #9
    Join Date
    May 2004
    Location
    Seattle
    Posts
    1,313
    zustimmung

  10. #10
    Join Date
    Nov 2003
    Posts
    2,935
    Provided Answers: 12
    Quote Originally Posted by blindman
    Anyone who equates the two does not understand the concept of a null value
    I do understand the concept of a null value

    A zero-length string has a known value.
    And what is this "value"?
    Can you have a similar distinction for a numeric column or a date column? (i.e. an "empty" value which is not null?)

    The point is, that I think that an empty string is essentially the same as a null value. It means "I don't have data for it".

    When it comes to other data types then nobody seems to want this distinction between an "empty" value and a null value. So what's so special about a character value that makes it different from other data types?

  11. #11
    Join Date
    Mar 2006
    Posts
    53
    The value of an empty string is 'empty'. The value of null is 'unknown'. There is a difference. (For one thing, one unknown value cannot be determined to be equal to another unknown value but 'empty' is definitely equal to 'empty'.)

    FWIW our standard is to avoid nulls whenever an appropriate default value can be determined. Our standard default character value is the empty string so Oracle's null handling usually works for us on a practical level-but it's not ideal & definitely not ANSI-compliant. It'd be nice if you could turn that behavior on & off according to the database. Mostly I like defaulting nulls to empty strings & having a database setting for it would eliminate mistakes-but sometimes I *don't* want nulls to default to empty strings. Then, I'd like to be able to turn that 'automatic default' off.
    Now back on Oracle. The more things change, the more they stay the same.

  12. #12
    Join Date
    Nov 2003
    Posts
    2,935
    Provided Answers: 12
    Quote Originally Posted by calvin-c
    The value of an empty string is 'empty'. The value of null is 'unknown'.
    Then why doesn't anybody need this concept (empty vs. unknown) for non-character data types?
    In my(!) opinion there is no difference between empty and unknown.

  13. #13
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Quote Originally Posted by shammat
    I do understand the concept of a null value
    Ok maybe you do understand the concept of a null value. I'll give you the benefit of the doubt for now.

    Quote Originally Posted by shammat
    And what is this "value"?
    Zzzzzzt! Doubt removed!

    Quote Originally Posted by shammat
    The point is, that I think that an empty string is essentially the same as a null value. It means "I don't have data for it".
    Zzzzzzt again! A null value means you don't have data for it. A zero-length string means there is no data for it.

    Quote Originally Posted by shammat
    In my(!) opinion there is no difference between empty and unknown.
    Then your opinion is wrong.

    Here is an empty box: []
    Here is a box which may be empty, but which may contain $1,000,000: []

    Would you say the two are of equal value? Would you trade the box who's contents are unknown for the box which is known to be empty?

    Well? Deal or no deal?

    Do not feel bad. You are not alone. The concept of a NULL value is strangely difficult to grasp for many people. A common post of the SQL Server forums is something like "How come when I execute SELECT * FROM TABLE WHERE COLUMNVALUE = NULL I don't get any rows returned?"
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  14. #14
    Join Date
    Mar 2006
    Posts
    53
    The concept really isn't empty vs. unknown, it's 'known' vs. unknown. Empty string just happens to be a convenient default (known) value for character data. The concept exists for non-character data too, it's just more difficult figuring out the appropriate default value. What's the appropriate default for Boolean?
    Now back on Oracle. The more things change, the more they stay the same.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •