Automatically decode hex for BLOB fields.

[dotancohen]

LiteCLI has a terrific feature in which BLOB fields (e.g. used to store an UUID primary key) are automatically encoded for display as hex:

CREATE TABLE notes (
    id BLOB PRIMARY KEY,
    content TEXT NOT NULL
)

notes.db>
+------------------------------------+---------------------------------------------------+
| id                                 | content                                           |
+------------------------------------+---------------------------------------------------+
| 0x019b5525a6f878709a20d7da96f5e599 | Note the first.                                   |
+------------------------------------+---------------------------------------------------+

However, that hex value can not be used in a query:

notes.db> select * from notes where id = '0x019b5525a6f878709a20d7da96f5e599';
0 rows in set
Time: 0.000s

notes.db> select * from notes where id = '019b5525a6f878709a20d7da96f5e599';
0 rows in set
Time: 0.000s

Please consider automatically decoding hex values used on BLOB fields.

For reference, this is what the SELECT query looks like in sqlite3:

sqlite> select id, content from notes;
+------------+---------------------------------------------------+
|     id     |                      content                      |
+------------+---------------------------------------------------+
|            | Note the first.                                   |
| �U%��xp� �ږ��� |                                                   |
+------------+---------------------------------------------------+

[kracekumar]

I tried to replicate this, but couldn't get it. Can you confirm what i am doing is correct here? If not, can you tell what is incorrect?

$sqlite3
SQLite version 3.43.2 2023-10-10 13:08:14
Enter ".help" for usage hints.
Connected to a transient in-memory database.
Use ".open FILENAME" to reopen on a persistent database.
sqlite> CREATE TABLE notes (
(x1...>     id BLOB PRIMARY KEY,
(x1...>     content TEXT NOT NULL
(x1...> )
   ...> ;
sqlite> insert into notes(id, content) values('cf680228df6f4ddd8620fae70b426a8f', 'test');
sqlite> select * from notes;
cf680228df6f4ddd8620fae70b426a8f|test
sqlite> select id, content from notes;
cf680228df6f4ddd8620fae70b426a8f|test
sqlite> insert into notes(id, content) values('0x019b5525a6f878709a20d7da96f5e599 ', 'notes');
sqlite> select * from notes;
cf680228df6f4ddd8620fae70b426a8f|test
0x019b5525a6f878709a20d7da96f5e599 |notes
sqlite> select id from notes;
0x019b5525a6f878709a20d7da96f5e599
cf680228df6f4ddd8620fae70b426a8f
sqlite> select from_hex(id) from notes;
Parse error: no such function: from_hex
  select from_hex(id) from notes;
         ^--- error here
sqlite> insert into notes(id, content) values('0x019b5525a6f878709a20d7da96f5e599', 'notes 2');
sqlite> select id from notes;
0x019b5525a6f878709a20d7da96f5e599
0x019b5525a6f878709a20d7da96f5e599
cf680228df6f4ddd8620fae70b426a8f
sqlite> select id, content from notes;
cf680228df6f4ddd8620fae70b426a8f|test
0x019b5525a6f878709a20d7da96f5e599 |notes
0x019b5525a6f878709a20d7da96f5e599|notes 2