How do I find the length (size) of a binary blob in sqlite

Question

I have an sqlite table that contains a BLOB file, but need to do a size/length check on the blob, how do I do that?

According to some documentation I did find, using length(blob) won't work, because length() only works on texts and will stop counting after the first NULL. My empirical tests have shown this to be true.

I'm using SQLite 3.4.2

---

Updates:

So as of SQLite 3.7.6 it appears as though the length() function returns the correct value of blobs - I checked various change-logs of sqlite, but did not see in what version this was corrected.

From Sqlite 3.7.6:

 payload_id|length(payload)|length(hex(payload))/2 1807913|194|194 1807914|171|171 

The documentation was changed to reflect this.

 length(X)   The length(X) function returns the length of X in characters if X is             a string, or in bytes if X is a blob. If X is NULL then length(X) is             NULL. If X is numeric then length(X) returns the length of a string              representation of X. 

Answer 1

haven't had this problem, but you could try length(hex(glob))/2

Update (Aug-2012): For SQLite 3.7.6 (released April 12, 2011) and later, length(blob_column) works as expected both both text and binary data.

Answer 2

for me length(blob) works just fine, gives the same results like the other.