Empty BLOBS - SQL Server

How do you select empty BLOBS from MS SQL server. I mean empty and not null. For exa开发者_运维知识库mple if you wanted an empty string you could do: select * from person where name = ''. How is this done for empty BLOBS?

This works for me when dealing with TEXT fields:

SELECT * FROM table WHERE LEN(SUBSTRING(blob,1,1))=0

One way is to compare the length with 0:

WHERE datalength(field) = 0

You could search for rows WHERE DATALENGTH(yourBlobCol) = 0

http://msdn.microsoft.com/en-us/library/ms173486.aspx

You could also cast to varchar(max) and compare to '' but the former is probably better.