Post reply

NULLS not behaving as expected

  • December 9, 2005 at 7:41 am

    #167383

    Working on a database where SET ANSI_NULLS is set to OFF. However, an example query might include:

     

    WHERE field1 <> '0'

     

    but the result does not return records where field1 is null. According to BOL, the boolean expression should return FALSE under these circumstances but it obviously isn't. Any ideas please.

     

    Thanks

  • December 9, 2005 at 7:43 am

    #608656

    Sorry, I meant TRUE for rows where field1 is null and therefore they should be returned.

  • February 9, 2006 at 1:16 pm

    #619914

    ANSI_NULLs can be set at the connection level -- perhaps it is not set as you expect in your connection?

    For example, executing this in SQL Server 2000 Query Analyzer, returns both different answers, one after the other:

    SET ANSI_NULLS ON

    SELECT CASE WHEN NULL=NULL THEN 'NULL=NULL' ELSE 'NULL!=NULL' END

    SET ANSI_NULLS OFF

    SELECT CASE WHEN NULL=NULL THEN 'NULL=NULL' ELSE 'NULL!=NULL' END

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply