SQLite User Forum

hex string conversion to blob in sql
Login
Chronological

hex string conversion to blob in sql

(1) By FruityMike on 2022-10-08 15:19:34 [link] [source] 

Hey,

So I'm trying to figure it out if it's possible to convert hex string to blob in sql.

So for example. There is a table:

CREATE TABLE "wallet" (
	"address"	TEXT NOT NULL,
	PRIMARY KEY("address")
)

And I want to store basically the same data as blob in:

CREATE TABLE "test" (
	"address"	BLOB NOT NULL,
	PRIMARY KEY("address")
)

Wallet table consists of entries like 0x5DF9B87991262F6BA471F09758CDE1c0FC1De734 . Which are string representations of 20 bytes. So, in order to do the migration I need to write a query which converts entries to blob with length of 20.

So far I have tried the following:

select length(cast(substr(address, 3) as BLOB))
from wallet limit 1;

But the length is 40, so I'm doing something wrong. Could anyone explain me what is wrong and how I could go about it in sql?

(2) By Keith Medcalf (kmedcalf) on 2022-10-08 16:19:04 in reply to 1 [link] [source]

Your application program can perform the conversion. Or you can write a custom extension function to do the conversion. Or you can write long-winded and inefficient SQL to do the converstion.

SQLite3 does not "interpret" data. It is a Relational Data Base Management System. It is concerned entirely and exclusively with the storage and retrieval of data and not the modification thereof.

(3.1) By Keith Medcalf (kmedcalf) on 2022-10-08 16:33:45 edited from 3.0 in reply to 1 [source]

That said, the following extension function will convert an "input text hex string" into a "blob". It is the opposite of the HEX function, but Oracle free.

/*
** 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((void*)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

(6) By ddevienne on 2022-10-10 07:20:32 in reply to 3.1 [link] [source]

I'd make it more generally useful, by ignoring the dashes used in uuid textual representations, to recover text uuids back to 16-byte blobs. Perhaps via an optional 2nd arg, for ignorable separators of the hex chars. My $0.02.

(7) By Keith Medcalf (kmedcalf) on 2022-10-10 10:37:44 in reply to 6 [link] [source]

The UUID functions are designed for playing UUIDs and can convert from text to blob UUIDs and v/v.

The unhex(...) function is the inverse of the hex(...) function.

hex(...) takes a blob argument (or converts it to a blob if it is not), and returns a string composed entirely of hex digits encoding the blob.

unhex(...) takes the output of hex(...) and returns the original blob. No oracle is included to undo any "conversion to blob" that was performed by the hex(...) function.

(4) By Keith Medcalf (kmedcalf) on 2022-10-08 16:29:55 in reply to 1 [link] [source] 

sqlite> select hex('Stuffs');
┌────────────────┐
│ hex('Stuffs')  │
├────────────────┤
│ '537475666673' │
└────────────────┘
VM-steps: 10
Run Time: real 0.008 user 0.000000 sys 0.000000
sqlite> select unhex('537475666673');
┌───────────────────────┐
│ unhex('537475666673') │
├───────────────────────┤
│ x'537475666673'       │
└───────────────────────┘

(5) By FruityMike on 2022-10-08 17:50:47 in reply to 4 [link] [source] 

Thank you for the comprehensive answer. I'm really not familiar with c/c++ and extending sqlite. I'll just write a script for it.