You will also note that the bytes in the blob must be the bytes in the
underlying database text encoding in order for a cast to text to produce
expected output (assuming that expected means valid text):
sqlite> pragma encoding;
UTF-8
sqlite> select x'414243';
ABC
sqlite> pragma encoding('utf-16le');
sqlite> select x'004100420043';
???
sqlite> select x'410042004300';
ABC
sqlite> pragma encoding('utf-16be');
sqlite> select x'004100420043';
ABC
So in order for "blobs" to be freely coerceable to text, you need to know the
database encoding, and if you get it wrong, your blob cannot be corerced to
text. This is why a blob is blob and text is text. As long as the
input/output text format and the database encoding are the same, there is
effectively no difference *in the raw bytes*. However, as soon as this is not
the case, conversions must be performed and a blob may not contain a valid byte
sequence in the underlying encoding.
--
The fact that there's a Highway to Hell but only a Stairway to Heaven says a
lot about anticipated traffic volume.
>-----Original Message-----
>From: sqlite-users <[email protected]> On
>Behalf Of Hick Gunter
>Sent: Monday, 16 December, 2019 00:42
>To: 'SQLite mailing list' <[email protected]>
>Subject: Re: [sqlite] [EXTERNAL] Difference between hex notation and
>string notation
>
>The X'' notation returns a blob. LIKE works with strings. Comparing a
>string to a blob of the same content always returns false.
>
>-----Ursprüngliche Nachricht-----
>Von: sqlite-users [mailto:[email protected]]
>Im Auftrag von Sascha Ziemann
>Gesendet: Freitag, 13. Dezember 2019 10:16
>An: [email protected]
>Betreff: [EXTERNAL] [sqlite] Difference between hex notation and string
>notation
>
>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'666163696c6974793d6461656d6f6e3b636f6d706f6e656e743d6e616d65643b746578
>743d7368757474696e6720646f776e');
>INSERT INTO LOG VALUES ('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
>
>This looks like a bug to me.
>
>Regards
>_______________________________________________
>sqlite-users mailing list
>[email protected]
>http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
>
>___________________________________________
> Gunter Hick | Software Engineer | Scientific Games International GmbH |
>Klitschgasse 2-4, A-1130 Vienna | FN 157284 a, HG Wien, DVR: 0430013 |
>(O) +43 1 80100 - 0
>
>May be privileged. May be confidential. Please delete if not the
>addressee.
>_______________________________________________
>sqlite-users mailing list
>[email protected]
>http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
_______________________________________________
sqlite-users mailing list
[email protected]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
