Hi,

Is there any way to know whether the blob field is empty or it have some data in it?

I have to create a join, to find out all rows that have blob field is empty/nothing stored in it

Thanks,

Something "like":

select length(cast blob-col-name as VARCHAR(nnnn)) from table-name.

i tried
and getting multiple rows with one column having '200' as value

I said "something like." I expected you to figure out how to adapt it to your own needs, such as:

select * from Content WHERE length(cast(CT as VARCHAR(200))) = 0

The question is what "no data" really means. An empty BLOB could be interpreted like an empty string - it is well-defined information, which is different from NULL. So if "no data" means "is there a value or is it NULL", you would use a predicate "blob-column IS [NOT] NULL". If you are interested in BLOBs that have a size of 0, simply use the LENGTH function as in "LENGTH(blob-column) = 0".

p.s: You cannot cast a BLOB to a VARCHAR. One is binary data, the other is string data (with code page, yada yada...).

According to the OP he was able to do it, if understand his post above correctly. I haven't tried it myself.

Yes, to find the BLOB without anything stored in it is considered as NULL ( as you correctly mentioned above). To find out the length of the particular Blob field in each row, it shows the length of the string which is considered the link of the location of the Blob image. For finding the actual size of each BLOB image, you can add an additional field in your table which will store the image size during the time of insert only with the help of the API / application loading the blob files.