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

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 insertinsert 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 stringselect 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.