Convert hex to text using SQLite

Question

I am trying to convert from a string representing hex data to the textual encoding (ASCII, UTF-8, etc.) of the hex data using purely SQLite language. Essentially I want the functionality of the X'[hex]' syntax, but applied to a programmatically derived hex string.

I want, for example, select X(hex_data_string) from ..., which is not legal SQLite syntax.

Obviously in the above snippet, I would not necessarily be able to output the data if it was not in a valid textual encoding. That is, if hex_data_string contains control chars, etc., the X() should fail in some way. If this is possible, there would have to be a default character encoding or the desired character encoding would have to be specified somehow.

I am not asking about how to retrieve the hex data string value from the SQLite database and then use C or some other facility to convert it. I am trying to perform this conversion in pure SQLite because I have queries that I check which return a text representation of hex characters representing binary data. Most of the binary data is ASCII, so I want to be able to quickly view the content of the binary data in my query output when applicable.

Intuitively, I figured this could be accomplished by casting the hex data string to a blob and using hex() but that still returns the hex data string.

Any ideas?

Possible duplicates:

SQLite X'...' notation with column data

sqlite char, ascii function

score 7 · Answer 1 · answered May 07 '15 at 11:52

7

This is quite old but I was looking for the same thing so I thought I'd post:

It seems you can just cast hex data as a varchar to convert it to ascii.

ie: select cast(data as varchar) from some_table will return a string representation of a binary field (data).

answered May 07 '15 at 11:52

Mladen Mihajlovic

6,095
7
40
55

1

Had this issue with Splunk reading from a specific sqlite database. This fixed my issue. – Tyler Montney Aug 18 '21 at 00:47

score 1 · Accepted Answer · answered Mar 12 '13 at 18:22

1

This is not possible in pure SQLite.

As an embedded database, SQLite is designed to provide only pure database functions, and leave the program logic to the the application. You are supposed to retrieve the hex data string value from the SQLite database and then use C or some other facility to convert it.

If you control the program you're running the queries in, you could install a user-defined function that does this conversion.

answered Mar 12 '13 at 18:22

CL.

173,858
17
217
259

I am just viewing these queries in a SQLite shell, and I have to copy the hex string from the query results and convert it using a different utility. I'm using [SQLite Shell](http://www.sqlite.org/download.html) and [SQLite Database Browser](http://sqlitebrowser.sourceforge.net/), which don't allow easy text copying of query results. Obviously writing a wrapper would be simple, and apparently there are [more](http://www.google.com/search?q=sqlite+gui) of [these](http://stackoverflow.com/questions/1507506/what-are-good-open-source-gui-sqlite-database-managers) than the last time I looked. – taz Mar 13 '13 at 13:47

score 1 · Answer 3 · answered Dec 12 '14 at 13:44

This will not be fully useful since it can't really be used inline, but I have done this in the past when I just wanted to convert one row of data and not create/find another utility.

WITH RECURSIVE test(c,cur) as (
     select '','686F77647921'
   UNION ALL
    select c || char((case substr(cur,1,1) when 'A' then 10 when 'B' then 11 when 'C' then 12 when 'D' then 13 when 'E' then 14 when 'F' then 15 else substr(cur,1,1) end)*16
               + (case substr(cur,2,1) when 'A' then 10 when 'B' then 11 when 'C' then 12 when 'D' then 13 when 'E' then 14 when 'F' then 15 else substr(cur,2,1) end)),
substr(cur,3)
from test where length(cur)>0
)
select * from test

Convert hex to text using SQLite

3 Answers3