How do I 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

---

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.

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.

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

As an additional answer, a common problem is that sqlite effectively ignores the column type of a table, so if you store a string in a blob column, it becomes a string column for that row. As length works different on strings, it will then only return the number of characters before the final 0 octet. It's easy to store strings in blob columns because you normally have to cast explicitly to insert a blob:

insert into table values ('xxxx'); // string insert
insert into table values(cast('xxxx' as blob)); // blob insert

to get the correct length for values stored as string, you can cast the length argument to blob:

select length(string-value-from-blob-column); // treast blob column as string
select length(cast(blob-column as blob)); // correctly returns blob length

The reason why length(hex(blob-column))/2 works is that hex doesn't stop at internal 0 octets, and the generated hex string doesn't contain 0 octets anymore, so length returns the correct (full) length.

Example of a select query that does this, getting the length of the blob in column myblob, in table mytable, in row 3:

select length(myblob) from mytable where rowid=3;

LENGTH() function in sqlite 3.7.13 on Debian 7 does not work, but LENGTH(HEX())/2 works fine.

# sqlite --version
3.7.13 2012-06-11 02:05:22 f5b5a13f7394dc143aa136f1d4faba6839eaa6dc

# sqlite xxx.db "SELECT docid, LENGTH(doccontent), LENGTH(HEX(doccontent))/2 AS b FROM cr_doc LIMIT 10;"
1|6|77824
2|5|176251
3|5|176251
4|6|39936
5|6|43520
6|494|101447
7|6|41472
8|6|61440
9|6|41984
10|6|41472