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

Question

I have an SQLite table that contains a BLOB I need to do a size/length check on. How do I do that?

According to documentation length(blob) only works on texts and will stop counting after the first NULL. My tests confirmed this. I'm using SQLite 3.4.2.

Ruthven

Answer 1

I 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 with both text and binary data.

Wuhu

Answer 2

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

Arboreous

For a binary blob? That would be in violation of the SQLite documentation which says blobs are treated as text and as such length() is accurate only until it finds a \0 character. – Ruthven

The current documentation at sqlite.org/lang_corefunc.html says "The length(X) function returns the length of X in characters if X is a string, or in bytes if X is a blob." – Overunder

Answer 3

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.

Indeterminism

Answer 4

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;

Distaff

Answer 5

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

Kirbee

Answer 6

Although length() should return bytes for BLOBs, this answer explains why it may not. One way is to use the STRICT clause on the table. Other way is to use octet_length() that has recently been added in 3.43.1.

Cabriolet