How to format SQLite BLOB columns as hex

by
, posted

I recently had a small problem: I was using the SQLite CLI and I wanted to see binary data in a BLOB column. When I did a normal SELECT on it, I got unreadable garbage:

SELECT my_blob_column FROM my_table;
-- ยดรต

I wanted to see the value in hex instead. After reading some documentation, I changed my query to use SQLite’s hex function, like so:

SELECT hex(my_blob_column) FROM my_table;
-- C2B4C3B5

Alternatively, the quote function also presents it in a readable way:

SELECT quote(my_blob_column) FROM my_table;
-- X'C2B4C3B5'

Now, I can make sense of the data in BLOB columns when I use the SQLite CLI. I hope this post helps somebody else!