On Monday, 13 January, 2020 02:27, Dominique Devienne <ddevie...@gmail.com> wrote:
>On Fri, Jan 10, 2020 at 7:03 PM Richard Hipp <d...@sqlite.org> wrote: >> On 1/10/20, Dominique Devienne <ddevie...@gmail.com> wrote: >>> There's no way at all, to know the length of a text column with >>> embedded NULLs? >> You can find the true length of a string in bytes from C-code using >> the sqlite3_column_bytes() interface. But I cannot, off-hand, think >> of a way to do that from SQL. > That's what I thought. Which implies length(text_val) is O(N), while > length(blob_val) is O(1), something I never quite realized. And this > despite both storing the length the same way at the file-format level. > That's kind of a gotcha, which might be worth documenting. No, they are measured in different units. length(something) where something is NOT a text value returns the length in BYTES of that something. For a BLOB this is the number of bytes according to the length data stored in the database. For NULL, INTEGER, or REAL values, it is the number of BYTES in the ASCII text representation of that thing (which also happens to be the number of characters since each ASCII character takes one byte). length(something) where something IS a text value returns the number of codepoints in the UTF-8 representation of that text and counting stops at the zero terminator (since the definition of "text" is a C string, all text values have an appended 0 terminator -- if there is an embedded 0 character, that terminates the counting). >> I'd vote for a lengthof(col) that's always O(1) for both text and blob >> values, although I'm not holding my breath for it, as I suspect it's >> unlikely to be added, given its relative low value. So what should lengthof(something) return the number of bytes in the 'database encoding' or something else? -- The fact that there's a Highway to Hell but only a Stairway to Heaven says a lot about anticipated traffic volume. _______________________________________________ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users