Selecting not null column

Question

I have a table with varbinary(max) column and nvarchar(max) column. One of them is null and the other has a value.

I would like to return the column that has the value as a varbinary(max) column. So far I have tried this, that does not work:

SELECT 
      A =  
      CASE A
         WHEN NULL THEN B
         ELSE A 
      END
FROM Table

Answer 1

Try SELECT ISNULL(A, cast(B AS varbinary(max))) FROM TABLE

Answer 2

SELECT COALESCE(A, CAST(B As varbinary(max)))

UPDATE: In response to comments (thanks) and assuming B is the nvarchar(max) column, I have moved the CAST inside the COALESCE

Answer 3

Your case statement evaluates to the dreaded A = NULL:

CASE A WHEN NULL THEN B ELSE A END

Is the same as:

CASE WHEN A = NULL then B ELSE A END

One way to fix this is to use A IS NULL, like:

CASE WHEN A IS NULL THEN B ELSE A END

Or even simpler:

COALESCE(A,B)

Both the when and the coalesce will assume the data type of the first argument. To cast the result to varbinary, you can place the varbinary column first, or explicitly cast:

COALESCE(CAST(A AS VARBINARY(MAX)),B)