Sqlite – How to find the length (size) of a binary blob in sqlite


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


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:


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.

Best Solution

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.