SQLite Forum

HEX - What is the reverse function?

HEX - What is the reverse function?

(1) By anonymous on 2020-08-30 06:21:31 [link] [source]

How do I get the original values , i.e. reverse HEX?


sqlite> .mode column
sqlite> select Hex(42),hex("answer to life");
Hex(42)  hex("answer to life")
-------  ----------------------------
3432     616E7377657220746F206C696665

How do I get back the original values?

(2) By luuk on 2020-08-30 08:00:53 in reply to 1 [link] [source]


claims that is can be done using:

select cast(data as varchar) from some_table

but, that does not seem to work.


shows a piece of SQL code which works:

WITH RECURSIVE test(c,cur) as (
select '','686F77647921'
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)),
from test where length(cur)>0
select * from test

(5) By anonymous on 2020-08-30 08:55:18 in reply to 2 [link] [source]

Change final select to

select * from test group by true having max(length(c));

to get only the full length string result.

(3) By Keith Medcalf (kmedcalf) on 2020-08-30 08:02:16 in reply to 1 [link] [source]

You would have to write one.

The builtin HEX function takes one parameter, reads it as a blob (so numeric values -- INTEGER/REAL/NUMERIC are converted to text first), and then outputs the hex string (text) corresponding to that bag-o-bytes.

The function is not reversible unless you have an oracle to provide you with the original datatype (TEXT/BLOB/INTEGER/REAL/NUMERIC).

(4) By jchd (jchd18) on 2020-08-30 08:28:53 in reply to 1 [link] [source]

select hex('Price is 3€'), cast(x'50726963652069732033E282AC' as text)

(6) By Keith Medcalf (kmedcalf) on 2020-08-30 08:58:45 in reply to 4 [link] [source]

Good example assuming that you have an oracle.

select hex(42), cast(x'3432' as integer);

(8) By jchd (jchd18) on 2020-08-30 09:27:19 in reply to 6 [link] [source]

Yeah, as usual: know your data!

Can as well be done with extension eval(), but application code is another way.

(9) By anonymous on 2020-08-30 09:32:43 in reply to 6 [link] [source]

sqlite> select hex(42), cast(x'3432' as typeof(42)) as originalValue;
hex(42)  originalValue
-------  -------------
3432     42

In my case, the type to restore to is always known (BLOB). 

But I am unable to get the original value from the hex(?) value stored in a table.

(7) By anonymous on 2020-08-30 09:24:11 in reply to 4 [link] [source]

Thanks; your expression works.

However, setting output option for clarity, thus: 

.mode column
.headers on


sqlite> /* First attempt with prefix x */
sqlite> SELECT cast('x' || hex('Price is 3€') AS TEXT) AS originalValue;
sqlite> /* Second attempt without prefix x*/
sqlite> WITH cte (hexvalue)
   ...> AS (
   ...> SELECT hex('Price is 3€')
   ...> )
   ...> SELECT cast(hexvalue AS TEXT)
   ...> FROM cte;
cast(hexvalue AS TEXT)
sqlite> /* Third attempt .. from a table */
sqlite> create temp table tmp as select hex('Price is 3€') as hexValue;
sqlite> select cast(hexValue as text) from tmp;
cast(hexValue as text)

I cannot see why neither of the three ways I tried returns the original value. Any ideas?

(10) By Keith Medcalf (kmedcalf) on 2020-08-30 23:04:24 in reply to 7 [link] [source]

Because no matter how much you play with data it is still data.

You would have to write a loadable function which converts "data" that consists of a bunch of hex digits in text format into a bag-o-bytes composed of the interpretation of those hex digits.

SQLite3 does NOT contain a function called, for example, UNHEX(arg) which takes a text string consisting of a bunch of hexidecimal digits and returns a blob (bag-o-bytes) containing the conversion of the data from one format to the other.

There are many very inefficient ways to get around the fact that such a function does not exist. For example, you can play with recursive CTE's to do the decoding, you can construct a constant from your data and have it dynamically executed, or any number of other work-arounds.

However there is no builtin UNHEX function that reverses the operation performed by the HEX function. If you want one, then you can write one.

(11) By Keith Medcalf (kmedcalf) on 2020-08-31 00:08:21 in reply to 10 [link] [source]

Here is a sample UNHEX loadable function. It is crude but it works.

** Function UNHEX(arg) -> blob
** Decodes the arg which must be an even number of hexidecimal characters into a blob and returns the blob

#ifdef __cplusplus
extern "C" {

    #define SQLITE_PRIVATE static

#include <stdlib.h>
#include <string.h>

    #include "sqlite3.h"
        #include "config.h"
    #include "sqlite3ext.h"

#ifndef _WIN32_WINNT
#define _WIN32_WINNT 0x0600

static void _unhexFunc(sqlite3_context *context, int argc, sqlite3_value **argv)
    long olength = sqlite3_value_bytes(argv[0]);
    long length;
    unsigned char* data = (unsigned char*)sqlite3_value_text(argv[0]);
    unsigned char* blob;
    unsigned char* stuff;
    unsigned char buffer[4] = {0};

    if ((olength % 2 != 0) || (olength < 2))

    blob = malloc(length / 2);
    stuff = blob;
    length = olength;

    while (length > 0)
        memcpy(buffer, data, 2);
        *stuff = (unsigned char)strtol(buffer, NULL, 16);
        data += 2;
        length -= 2;
    sqlite3_result_blob(context, blob, olength/2, SQLITE_TRANSIENT);

#ifdef _WIN32
int sqlite3_sqlunhex_init(sqlite3 *db, char **pzErrMsg, const sqlite3_api_routines *pApi)

    return sqlite3_create_function(db, "UNHEX", 1, SQLITE_UTF8|SQLITE_DETERMINISTIC|SQLITE_INNOCUOUS, 0, _unhexFunc,  0, 0);

#ifdef __cplusplus

And you would use it thusly:

SQLite version 3.34.0 2020-08-30 09:09:04
Enter ".help" for usage hints.
Connected to a transient in-memory database.
Use ".open FILENAME" to reopen on a persistent database.
sqlite> .load sqlunhex
sqlite> select hex('howdy doody time!');
│      hex('howdy doody time!')      │
│ 686F77647920646F6F64792074696D6521 │
sqlite> select unhex('686F77647920646F6F64792074696D6521');
│ unhex('686F77647920646F6F64792074696D6521') │
│ howdy doody time!                           │
sqlite> select unhex(hex('Howdy Doody Time!'));
│ unhex(hex('Howdy Doody Time!')) │
│ Howdy Doody Time!               │

(12) By anonymous on 2020-08-31 06:49:09 in reply to 11 [link] [source]

- Why does SQLite have the HEX function? 
- How is it meant to be used?

(13) By Tim Streater (Clothears) on 2020-08-31 07:39:45 in reply to 12 [source]

I've occasionally found it useful when debugging and I want to know exactly what set of bytes is in a column, without that set being interpreted by some intermediate bit of software.

(14) By Richard Hipp (drh) on 2020-08-31 11:22:14 in reply to 12 [link] [source]

Common use cases for the hex() function include:

  • Convert a binary blob into human-readable format.
  • Generate a unique identifier or random password using an expression like: "hex(randomblob(20))"
  • Generate human-readable hash strings from extension functions like sha3() that otherwise return a binary blob. Ex: "hex(sha3(...))"
  • To view the actual UTF8 (or UTF16) of a text string.