Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

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

Tags:

casting

sqlite

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?

like image 902
preahkumpii Avatar asked Jan 21 '13 08:01

preahkumpii


People also ask

How do I change BLOB to TEXT?

If you want to update the BLOB datatype column to the TEXT datatype column. Follow these steps: Alter the table and add a column having data type TEXT. Add content to that column after converting BLOB data to TEXT date.

Can you TEXT a BLOB?

The four BLOB types are TINYBLOB , BLOB , MEDIUMBLOB , and LONGBLOB . These differ only in the maximum length of the values they can hold. The four TEXT types are TINYTEXT , TEXT , MEDIUMTEXT , and LONGTEXT . These correspond to the four BLOB types and have the same maximum lengths and storage requirements.

Does SQLite support a BLOB type?

(10) Does SQLite support a BLOB type? SQLite allows you to store BLOB data in any column, even columns that are declared to hold some other type. BLOBs can even be used as PRIMARY KEYs.

What is BLOB data type in SQLite?

A BLOB (large binary object) is an SQLite data type that stores large objects, typically large files such as images, music, videos, documents, pdf, etc. We need to convert our files and images into binary data (byte array in Python) to store it into SQLite database.


1 Answers

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.

like image 178
scruss Avatar answered Sep 20 '22 10:09

scruss