casting - Sqlite: How to cast(data as TEXT) for BLOB

Question

I have a sqlite database from which I want to extract a column of information with the datatype BLOB. I am trying this:

SELECT cast(data as TEXT) FROM content

This is obviously not working. The output is garbled text like this:

x??Uak?0?>?8?0Ff;I?.??.i?%?A??s?M

The data in the content column is mostly text, but may also have images (which I recognized could cause a problem if I cast as TEXT). I simply want to extract that data into a usable format. Any ideas?

Answer 1

You can use

SELECT hex(data) FROM content

or

SELECT quote(data) FROM content

The first will return a hex string (ABCD), the second quoted as an SQL literal (X'ABCD').

Note that there's (currently) no way of converting hexadecimal column information back to a BLOB in SQLite. You will have to use C/Perl/Python/… bindings to convert and import those.