SQLite extension: Hex str to int conversion.

hextoint.c

/*
** 2025-01-01
**
** The author disclaims copyright to this source code.
*/

#include "sqlite3ext.h"
SQLITE_EXTENSION_INIT1

#include <stdlib.h>
#include <stdio.h>

/*
** Convert hex string to integer. Allow NULL to pass through,
** otherise the value must be a non-empty hex string with an optional
** '0x' or '0X' prefix.
*/
static void hextoint(
  sqlite3_context *context,
  int argc,
  sqlite3_value **argv
){
  sqlite_int64 result;
  int base;
  const unsigned char *start;
  char *stop;

  sqlite3_value *value = argv[0];
  int value_type = sqlite3_value_type(value);

  switch (value_type) {
    case SQLITE_NULL:
      sqlite3_result_null(context);
      return;
    case SQLITE_TEXT:
      start = sqlite3_value_text(value);
      result = strtoll((const char *)start, &stop, 16);
      if (*start == '\0' || *stop != '\0')
        break;
      sqlite3_result_int64(context, result);
      return;
  }
  sqlite3_result_error(context, "expects hex string or NULL", -1);
}

#ifdef _WIN32
__declspec(dllexport)
#endif
int sqlite3_hextoint_init(
  sqlite3 *db, 
  char **pzErrMsg, 
  const sqlite3_api_routines *pApi
){
  int rc = SQLITE_OK;
  SQLITE_EXTENSION_INIT2(pApi);
  (void)pzErrMsg;  /* Unused parameter */

  sqlite3_create_function(db, "hextoint", 1, SQLITE_UTF8|SQLITE_DETERMINISTIC, 0, hextoint, 0, 0);
  return rc;
}

I know that this gist is about an extension. I myself had struggle to solve this missing function, but then I found out that converting a hexstring into an integer can be done via hexstr->>'$' using the JSON features introduced by version 3.38.0 (2022-02-22).

See also a more detailed description on Stack Overflow