SQLite Forum

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>
```