2

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.)

CL.
  • 173,858
  • 17
  • 217
  • 259
cemerick
  • 5,916
  • 5
  • 30
  • 51

1 Answers1

2

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);
CL.
  • 173,858
  • 17
  • 217
  • 259
  • Good catch. `.mode quote` was added for sqlite `3.15.1`; I've been using `3.11.0`, which explains why I didn't see the option! Unfortunately, there's no way AFAICT to have both `quote` and `columns` modes, but I'll learn to live with it. :-) – cemerick Jan 03 '18 at 20:12