dealing with a null in a varbinary field RRS feed

  • Question

  • I'm trying to test a varbinary(max) field to see if it's null.  I'm trying to figure out if it's a null or not in the select statement, hoping to avoid having to do this locally for every row of the returned dataset.
    This is the SQL I'm using,
    SELECT attachment,
    TestResult = CASE attachment WHEN null THEN 'is a null' ELSE 'is not a null' END
    FROM Table1
    and this is the result I get...
       attachment		TestResult
     1 NULL			is not a null
     2 0xFFD8FFE0...	is not a null
    So it functions (which is a step up from trying to run that Case on an Image field, which is how I got started testing this before I go change types on an existing table), it's just not giving me a result that makes sense.
    Any ideas?  Thanks.
    Monday, August 3, 2009 6:46 PM


  • SELECT attachment,
    TestResult = CASE ISNULL(attachment,-1) WHEN -1 THEN 'is a null' ELSE 'is not a null' END
    FROM Table1
    A friend came thru for me offline.  There's the fix - make the Null values into something that's a bit more specific and test for that.
    WHY the null was troublesome for SQL remains a mystery.  But it works.
    • Marked as answer by CRB042 Monday, August 3, 2009 8:19 PM
    Monday, August 3, 2009 8:19 PM