Search code examples
sqlsql-servervarbinary

Differentiate 0x and 0x0000 varbinary columns

I have a table with a column of datatype VARBINARY(252). Some of the rows have a 0x value and most of them have a 0x0000000..... value in this VARBINARY column.

So how do I write a SELECT statement to fetch the rows with 0x value

-and-

a SELECT statement to fetch the rows with a 0x0000000..... value?

Any help is greatly appreciated.

Solution

  • Thanks to @Smor, I think I figured out the answer to my question. The value 252 is hard-coded because that is the max length of my datatype.

    SELECT *
FROM [Table1] 
WHERE [Col1] = 0x
    AND DATALENGTH([Col1]) <> 252