https://stackoverflow.com/questions/15366594/convert-hex-to-text-using-sqlite

claims that is can be done using:

select cast(data as varchar) from some_table

but, that does not seem to work.

https://html.developreference.com/article/23338803/Convert+hex+to+text+using+SQLite

shows a piece of SQL code which works:

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

Change final select to

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

to get only the full length string result.

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).

Good example assuming that you have an oracle.

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

Yeah, as usual: know your data!

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

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.

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" {
#endif

#ifndef SQLITE_PRIVATE
    #define SQLITE_PRIVATE static
#endif

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

#ifdef SQLITE_CORE
    #include "sqlite3.h"
#else
    #ifdef _HAVE_SQLITE_CONFIG_H
        #include "config.h"
    #endif
    #include "sqlite3ext.h"
    SQLITE_EXTENSION_INIT1
#endif

#ifndef _WIN32_WINNT
#define _WIN32_WINNT 0x0600
#endif

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))
    {
        return;
    }

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

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

#ifdef _WIN32
#ifndef SQLITE_CORE
__declspec(dllexport)
#endif
#endif
int sqlite3_sqlunhex_init(sqlite3 *db, char **pzErrMsg, const sqlite3_api_routines *pApi)
{
    SQLITE_EXTENSION_INIT2(pApi);

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

#ifdef __cplusplus
}
#endif

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!               │
└─────────────────────────────────┘
sqlite>

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.

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.

Keith, thanks for the extension! Is it OK to use it as public domain (or maybe under MIT License or other permissive license terms)?

Yes, that is fine. I would consider it Public Domain. There is no warranty, neither express nor implied. If you break it, you own both halves.

Thanks a lot!

This one will work better:

/*
** Function UNHEX(arg) -> blob
**
** Decodes the arg which must be an even number of hexadecimal characters into a blob and returns the blob
** Return NULL if arg wrong size or not all characters are hexadecimal
**
*/

#ifdef __cplusplus
extern "C" {
#endif

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

#ifdef SQLITE_CORE
    #include "sqlite3.h"
#else
    #include "sqlite3ext.h"
    SQLITE_EXTENSION_INIT1
#endif

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

    if ((olength % 2 != 0) || (olength < 2))
    {
        return;
    }
    blob = sqlite3_malloc(olength / 2);
    if (blob == 0)
    {
        sqlite3_result_error_nomem(context);
        return;
    }
    stuff = blob;
    length = olength;
    while (length > 0)
    {
        memcpy(buffer, data, 2);
        if ((!isxdigit(buffer[0])) || (!isxdigit(buffer[1])))
        {
            break;
        }
        *stuff = (unsigned char)strtol(buffer, NULL, 16);
        stuff++;
        data += 2;
        length -= 2;
    }
    if (length == 0)
    {
        sqlite3_result_blob(context, blob, olength/2, sqlite3_free);
    }
    else
    {
        sqlite3_free(blob);
    }
}

#ifdef _WIN32
#ifndef SQLITE_CORE
__declspec(dllexport)
#endif
#endif
int sqlite3_sqlunhex_init(sqlite3 *db, char **pzErrMsg, const sqlite3_api_routines *pApi)
{
    SQLITE_EXTENSION_INIT2(pApi);

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

#ifdef __cplusplus
}
#endif

Thank you very much ツ

there is no builtin UNHEX function

True, at the time. But it was introduced recently, and will be in 3.41.

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

This statement

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

hangs in version 3.40 - i.e. I get the perpetual prompt

...>

even after adding ;

Something to do with unicode i.e. €?

Works for me. What OS are you using? Where did you get your CLI binary? Did you download one of the pre-builts or did you build it yourself?

Apologies - I should have specified the details.

I am using:

1. Windows 11
2. CLI 32bit
3. Downloaded CLI as a pre- built binary

Here are my session(s):

SQLite version 3.40.0 2022-11-16 12:10:08
Enter ".help" for usage hints.
sqlite> select hex('Price is 3€'), cast(x'50726963652069732033E282AC' as text);
;
   ...> ;
   ...> ;
   ...>

Upgraded to 3.40.1

SQLite version 3.40.1 2022-12-28 14:03:47
Enter ".help" for usage hints.
sqlite> select hex('Price is 3€'), cast(x'50726963652069732033E282AC' as text)
;
   ...> ;
   ...> ;
   ...>

Same experience.

Notes:

1. In the 3.40.0 session, the SQL statement is terminated with semi-colon but in the 3.40.1 session it is not - makes no difference in what I experience.
2. On pressing ENTER after the statement, the cursor sits at the leftmost position on the next line, in both cases.
3. I copied the session while the CLI is still prompting for completion of the SQL statement - the only way to break free is to press CTRL + C or Ctrl + Break.

I could not reproduce this with sqlite3.exe from sqlite-tools-win32-x86-3400100.zip in a CMD.exe window in code page 850 (Latin-1).

Microsoft Windows [Version 10.0.19044.2486]
(c) Microsoft Corporation. All rights reserved.

C:\Users\knu>d:\bin\sqlite3.exe
SQLite version 3.40.1 2022-12-28 14:03:47
Enter ".help" for usage hints.
Connected to a transient in-memory database.
Use ".open FILENAME" to reopen on a persistent database.
sqlite> select hex('Price is 3€'), cast(x'50726963652069732033E282AC' as text)
   ...> ;
507269636520697320333F|Price is 3?
sqlite> .q

C:\Users\knu>mode

Status for device CON:
----------------------
:
:
    Code page:      850

, BUT I do get the same results as you in the code page for UTF-8 :

chcp 65001

-- 
Regards,
Kees Nuyt

I could not reproduce this with sqlite3.exe from sqlite-tools-win32-x86-3400100.zip in a CMD.exe window in code page 850 (Latin-1).

However, in your session

sqlite> select hex('Price is 3€'), cast(x'50726963652069732033E282AC' as text)
   ...> ;
507269636520697320333F|Price is 3?
sqlite> .q

you have lost € in the reverse trip.

BUT I do get the same results as you in the code page for UTF-8 :

My code page is the same:

C:\>chcp
Active code page: 65001

My computer details:

OS Name:                   Microsoft Windows 11 Home
OS Version:                10.0.22621 N/A Build 22621
OS Build Type:             Multiprocessor Free
System Type:               x64-based PC
System Locale:             en-gb;English (United Kingdom)
Input Locale:              en-gb;English (United Kingdom)
Hotfix(s):                 4 Hotfix(s) Installed.
                           [01]: KB5020880
                           [02]: KB5012170
                           [03]: KB5022303
                           [04]: KB5020487

Finally

Status for device CON:
----------------------
    Lines:          30
    Columns:        120
    Keyboard rate:  21
    Keyboard delay: 1
    Code page:      65001

you have lost € in the reverse trip.

Yes, and that is to be expected, because Euro may not be defined in code page 805.

I can add that input scanning works correctly in code page 65001 as long as no double-byte / UTF-8 characters are entered.

C:\Users\knu>d:\bin\sqlite3.exe
SQLite version 3.40.1 2022-12-28 14:03:47
Enter ".help" for usage hints.
Connected to a transient in-memory database.
Use ".open FILENAME" to reopen on a persistent database.
sqlite> select 'a' as a;
a
sqlite> select 'a' as a
   ...> ;
a
sqlite> select 'a€' as a
;
   ...> ;
   ...>

I think this indicates confusing byte-counters and character-counters. MS Windows is hard to program for, if you don't accept 5 or more levels of indirection.

-- 
Regards,
Kees Nuyt

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

Maybe it works when starting from text, but definitely not from blob.
So the new in 3.41 unhex() is the only way to correctly round-trip blobs.

sqlite> with t(v) as (select randomblob(16)) select typeof(v), hex(v), cast(v as text) from t;
┌───────────┬──────────────────────────────────┬─────────────────┐
│ typeof(v) │              hex(v)              │ cast(v as text) │
├───────────┼──────────────────────────────────┼─────────────────┤
│ blob      │ 8EFF6B3CD83F30E4870F9978EF30248C │ ??k<??0??x?0$?    │
└───────────┴──────────────────────────────────┴─────────────────┘

1. Why does the final query in my session, shown below, return a count of 0? To me it seems there is some issue with encoding?

2. Visually, I do not see a difference between lastname and unhex(hex(lastname))

3. The length of lastname and unhex(hex(lastname)) match.

sqlite> select length(lastname),length(unhex(hex(lastname))) from employees;
length(lastname)  length(unhex(hex(lastname)))
----------------  ----------------------------
5                 5
7                 7
7                 7
4                 4
7                 7
8                 8
4                 4
8                 8
sqlite> select lastname,unhex(hex(lastname)) from employees;
LastName  unhex(hex(lastname))
--------  --------------------
Adams     Adams
Edwards   Edwards
Peacock   Peacock
Park      Park
Johnson   Johnson
Mitchell  Mitchell
King      King
Callahan  Callahan
sqlite> select count(*) from employees where lastname=unhex(hex(lastname));
count(*)
--------
0
sqlite>

It gets weird: what is the data type of the result of unhex: it appears to be neither TEXT nor driven by affinity.

sqlite> select count(*) from employees where lastname=cast(unhex(hex(lastname)) as text);
count(*)
--------
8
sqlite>

` Also, is there some guideline/documentation with affinity and unhex? unhex appears to return neither text or be driven by affinity - see below:

sqlite> select hex(786),786=unhex(hex(786));
hex(786)  786=unhex(hex(786))
--------  -------------------
373836    0
sqlite> select hex(786),786=cast(unhex(hex(786)) as int);
hex(786)  786=cast(unhex(hex(786)) as int)
--------  --------------------------------
373836    1
sqlite>

Does unhex return blob? Are relational operators blob columns unreliable?

Why does the final query in my session, shown below, return a count of 0?

Most likely because you are comparing text and blob values.

what is the data type of the result of unhex()

It returns a blob.

hex() takes a blob as argument. When you pass in a text value, it is first implicitly converted to a blob, with perhaps surprising semantic.

hex() and unhex() are meant for blob values IMHO.

hex() takes a blob as argument

Not true - regardless of what happens internally - hex() takes anything as its argument.

sqlite> select hex('abc'),hex(12.34),hex(cast(12.34 as float));
hex('abc')  hex(12.34)  hex(cast(12.34 as float))
----------  ----------  -------------------------
616263      31322E3334  31322E3334

hex() and unhex() are meant for blob values

Why aren't errors reported when the arguments are not of type blob?

The result of hex() is consistently text.

sqlite> select typeof(hex(employeeid)),typeof(hex(lastname)),typeof(hex(null)) from employees where employeeid = 8;
typeof(hex(employeeid))  typeof(hex(lastname))  typeof(hex(null))
-----------------------  ---------------------  -----------------
text                     text                   text
sqlite>

The result of hex() is NOT consistently blob.

sqlite> select typeof(unhex(hex(cast(3.1234 as float)))),typeof(unhex(cast(3.1234 as float))),typeof(unhex(null));
typeof(unhex(hex(cast(3.1234 as float))))  typeof(unhex(cast(3.1234 as float)))  typeof(unhex(null))
-----------------------------------------  ------------------------------------  -------------------
blob                                       null                                  null
sqlite>

It is impossible to tell apart the result in the last two columns in the example above.

I'd expect unhex() EITHER to return text OR (this might be a tall order) the type that was submitted to hex() in a round trip.

That would be an exceptionally tall order. Even assuming the desirability of such behavior, it would require either complex compile-time semantics and analysis, together with some runtime adjunct, or a side-channel for values beyond the single type/value pair now existent. Documenting it would be a challenge, in the writing, the reading, and the wondering why.

Not true ...

When restored to the context from which you lifted it, the statement which you purport to contradict is clearly true. You should be very careful to maintain relevant context when electing to contradict people. To do otherwise is often considered sneaky.

... Why aren't errors reported when the arguments are not of type blob?

Because all values which are not NULL may be converted to a blob.