length() on a blob returns the length of the blob without examining the blob
itself. The length is encoded in the data structure.
length() on text returns the number of characters in the text. This requires
retrieving the data and counting.
Whether sqlite stores a blob or text in the column depends on what you pass it.
For example:
sqlite> create table x(x blob);
sqlite> insert into x values ('x');
sqlite> insert into x values (cast('x' as blob));
sqlite> select * from x;
x
x
sqlite> select typeof(x) from x;
text
blob
So, if you insert text into a column, text is stored. If you insert a blob
into a column, a blob is stored. Column affinity is (as in all cases)
practically irrlevant. What you put will depend on how you put it (if inline
as in the above example), or whether you use bind_blob or bind_text when
storing the data -- just as what you get out depends on the type you ask for
when retrieving the data.
---
Theory is when you know everything but nothing works. Practice is when
everything works but no one knows why. Sometimes theory and practice are
combined: nothing works and no one knows why.
>-----Original Message-----
>From: sqlite-users-bounces at mailinglists.sqlite.org [mailto:sqlite-users-
>bounces at mailinglists.sqlite.org] On Behalf Of David Barrett
>Sent: Sunday, 1 March, 2015 11:41
>To: General Discussion of SQLite Database
>Subject: [sqlite] Does length() use strlen() on blobs?
>
>Hi! If I define a column as BLOB type, will length() run strlen() on the
>data, or will it consult some kind of internal value to determine the
>length? The documentation says:
>
>"For a string value X, the length(X) function returns the number of
>characters (not bytes) in X prior to the first NUL character. ... For a
>blob value X, length(X) returns the number of bytes in the blob."
>
>This suggests to me it uses some internal length value (eg, not strlen),
>but given that sqlite doesn't use strong typing, I'm wondering if I
>insert
>a string into that blob if it'll return the blob length or the string
>length.
>
>I ask because I'm going to be using sqlite on a performance-sensitive
>application to store small strings (as blobs), and one of the most
>frequent
>queries will be to get the length of the blob. I'm wondering if I should
>create a separate length column, or if I should just use length().
>
>Thanks!
>
>-david
>_______________________________________________
>sqlite-users mailing list
>sqlite-users at mailinglists.sqlite.org
>http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
