On Friday, 13 December, 2019 02:16, Sascha Ziemann <cev...@gmail.com> wrote:
>I have a problem to find rows in a database when I write in hex notation:
>CREATE TABLE LOG (MSG VARCHAR(6291456) NOT NULL);
>INSERT INTO LOG VALUES
>(X'666163696c6974793d6461656d6f6e3b636f6d706f6e656e743d6e616d65643b746578743d7368757474696e6720646f776e');
>INSERT INTO LOG VALUES ('facility=daemon;component=named;text=shutting down');
Yes, those are identical byte sequences, however, the first one is a "blob" and
the second one is "text":
SELECT ROWID, typeof(MSG), MSG FROM LOG;
1|blob|facility=daemon;component=named;text=shutting down
2|text|facility=daemon;component=named;text=shutting down
>SELECT ROWID,MSG FROM LOG; --
>returns both rows
>SELECT ROWID,MSG FROM LOG WHERE MSG LIKE '%down'; --
>returns just the second
>SELECT ROWID,MSG FROM LOG WHERE CAST(MSG AS VARCHAR) LIKE '%down'; --
>returns both rows
The version of the SQLite3 library that you are using must have been compiled
with the SQLITE_LIKE_DOESNT_MATCH_BLOBS preprocessor symbol defined. If you
want LIKE to match against blobs, do not define this preprocessor symbol when
compiling SQLite3.
When you cast msg to text it is now text and not a blob and therefore LIKE can
match it.
--
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
