57

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.

user4157124
  • 2,809
  • 13
  • 27
  • 42
Petriborg
  • 2,940
  • 3
  • 28
  • 49

5 Answers5

59

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.

BusterZo
  • 3
  • 3
Javier
  • 60,510
  • 8
  • 78
  • 126
13

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

Kenny Evitt
  • 9,291
  • 5
  • 65
  • 93
Chris
  • 131
  • 1
  • 2
  • 3
    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. – Petriborg Sep 24 '10 at 10:55
  • 2
    The current documentation at http://www.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." – Govert Nov 12 '10 at 22:21
9

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.

Community
  • 1
  • 1
Remember Monica
  • 3,897
  • 1
  • 24
  • 31
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;
ubzack
  • 1,878
  • 16
  • 16
2

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
user3336503
  • 33
  • 5