Search code examples
sqliteblobliteralsquoting

How to *always* display blob value using x'abc' binary string literal syntax?

Very similar to How to display blob value using x'abc' binary string literal syntax?:

How can I have the sqlite3 shell display always display blob columns using the hex notation, as per e.g. quote(blob_column_name), without explicitly using quote, and in select * queries (and other contexts where blob_column_name isn't mentioned explicitly)?

(I suspect the answer is "you can't", but I'm hoping to be pleasantly surprised.)

Solution

  • There are two output modes that use SQL syntax (not only for blobs but for all values):

    sqlite> .mode quote
sqlite> SELECT 1, x'123ABC', 'hello', null;
1,X'123abc','hello',NULL
sqlite> .mode insert
sqlite> SELECT 1, x'123ABC', 'hello', null;
INSERT INTO "table" VALUES(1,X'123abc','hello',NULL);