Geek Answers Handbook

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

I have a sqlite table that contains a blob file, but you need to do size / length check on blob, how to do it?

According to some documentation I found, using length (blob) will not work, because length () only works with texts and will stop counting after the first NULL. My empirical tests have shown that this is true.

I am using SQLite 3.4.2

Update:

Since SQLite 3.7.6 seems like the length () function is returning the correct blobs value - I checked various sqlite change logs, but did not see which version it was fixed in.

From Sqlite 3.7.6:

payload_id | length (payload) | length (hex (payload)) / 2
1807913 | 194 | 194
1807914 | 171 | 171

The documentation has been modified 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.
+31
source share
5 answers

there was no this problem but you can try length(hex(glob))/2

Update (August-2012): For SQLite 3.7.6 (released April 12, 2011) and later length(blob_column), both text and binary data work as expected.

+35
source

for me it length(blob)works just fine, gives the same results as others.

+9
source

, sqlite , , blob, . - , 0. blob, blob:

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

, , length blob:

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

, length (hex (blob-column))/2, , hex 0, 0 , ( ) .

One character stored in one byte can be two hexadecimal strings.

+6
source

An example of a query selectthat does this, getting the blob length in a column myblob, in a table mytable, in row 3:

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

The LENGTH () function in sqlite 3.7.13 does not work on Debian 7, 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
+2
source

More articles:


All Articles