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](https://stackoverflow.com/questions/10213677/sqlite-x-notation-with-column-data)

[sqlite char, ascii function](https://stackoverflow.com/questions/11604766/sqlite-char-ascii-function)