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

sqlite size blob

Petriborg · Oct 30, 2008 · Viewed 33.7k times · Source

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

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.

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

Answer

Related questions