Requesting an enhancement - a function that takes a hexstring and converts into into an integer (or fails). ('from_hex_string'?).

The specific usefulness for this would be to able to load in UNICODE tables - typically they show the code-point in a string hex-format- which would be nice to load-up directly into a sqlite table.

There are plenty of ways around this - using external scripts - but it might be a nice-to-have.

Background:

To output a unicode code character using a hex-literal, I can do the following. This example is the emoji 'grinning face' - which has the code '1F600':

sqlite> SELECT char(0x1f600) AS face;
face
😀

So that works just fine. However; it would be handy to keep the codes in a table - loaded from text values of that hex number- however the SQL Language Expressions section described that this isn't possible (with a CAST at least):

"SQLite only understands the hexadecimal integer notation when it appears in the SQL statement text, not when it appears as part of the content of the database."

So - is there any way of converting a hex-string into an integer? The corresponding decimal conversion works just fine:

sqlite> SELECT CAST('123' AS integer) AS i;
i
123

But attempting a hex-string behaves just like the documentation says (returns zero):

sqlite> SELECT CAST('0x1f600' AS integer) AS i;
i
0

The solution now is to use an external script to run through the data and pre-convert to decimal.

The following Python code illustrates the method:

from ast import literal_eval;
hexstring='0x1F600';
hex_literal=0x1F600;

assert(literal_eval(hexstring)==hex_literal);

Or alternatively just to output INSERT statements in the literal hex format for SQLite.

sqlite> SELECT CAST('0x1f600' AS integer) AS i;

You do NOT need to CAST HEX numbers: SQLite understands HEX numbers:

sqlite> SELECT 0x1f600 AS i,10* 0x1f600 AS j;
i       j
------  -------
128512  1285120

Thanks Ryan and Aask: but these two examples are still using the literal ('sql text') forms - which is what I was using in my first example.

I was asking (just 'sugar' really) for a function which could take a string and (attempt) to convert it to an integer.

Just because a lot of data already exists in that format. (Or close to it).

Yeah, not possible currently I think, and it's primitively easy to add the function yourself, but I can see the request to add it to core.

btw - did this for fun, which does the job, but is obviously slower than a native function. Persisting the view to a table with suitable index will also be much faster (obviously):

CREATE VIEW hexChars AS
WITH RECURSIVE charRange(intCharValue) AS (
    SELECT 57344 UNION ALL SELECT intCharValue+1 FROM charRange WHERE intCharValue < 129689
), toHex(icv, hexString, hexRemainder) AS (
    SELECT intCharValue, '', intCharValue FROM charRange
    UNION ALL
    SELECT icv, substr('0123456789ABCDEFx',(hexRemainder%16)+1,1)||hexString, (hexRemainder/16)
      FROM toHex
     WHERE hexRemainder > 0
)
SELECT hexString, intCharValue, char(intCharValue) AS unicodeChar
  FROM charRange
  JOIN toHex ON icv = intCharValue AND hexRemainder = 0
;


  -- Example 1:

SELECT * FROM hexChars WHERE hexString = '1F600'

  --          |            |unicodeC-
  -- hexString|intCharValue|   har   
  -- ---------|------------|---------
  -- 1F600    |   128512   |    😀   



  -- Example 2:

SELECT HexVal, Name, unicodeChar||unicodeChar||unicodeChar AS UCVal
  FROM (
         SELECT '1F600' AS HexVal, 'SmileyFace' AS Name UNION ALL
         SELECT '104B3',           'Lambda'   UNION ALL
         SELECT '1F160',           'Black-Q'  UNION ALL
         SELECT '1F30E',           'Earth'    UNION ALL
         SELECT '1F369',           'Doughnut' UNION ALL
         SELECT '1F39B',           'Domino'   UNION ALL
         SELECT '1F3A9',           'Top Hat'  UNION ALL
         SELECT '1F3B9',           'Piano'    UNION ALL
         SELECT '1F354',           'Hamburger'
       ) AS X
  JOIN hexChars ON hexString = X.HexVal


  -- HexVal |Name        |UCVal   
  -- -------|------------|--------
  -- 1F600  |SmileyFace  |😀😀😀  
  -- 104B3  |Lambda      |𐒳𐒳𐒳  
  -- 1F160  |Black-Q     |🅠🅠🅠  
  -- 1F30E  |Earth       |🌎🌎🌎  
  -- 1F369  |Doughnut    |🍩🍩🍩  
  -- 1F39B  |Domino      |🎛🎛🎛  
  -- 1F3A9  |Top Hat     |🎩🎩🎩  
  -- 1F3B9  |Piano       |🎹🎹🎹  
  -- 1F354  |Hamburger   |🍔🍔🍔  

Excellent ! 😀 I take my 0x1F3A9 off to you !

SQLite version 3.38.2 2022-03-26 13:51:10
Enter ".help" for usage hints.
Connected to a transient in-memory database.
Use ".open FILENAME" to reopen on a persistent database.
sqlite> .mode qbox
sqlite> .load sqlite3.dll
sqlite> select horner('00001010',2) as Bin, horner('012',8) as Oct, horner('0010',10) as Dec, horner('000A',16) as Hex;
┌─────┬─────┬─────┬─────┐
│ Bin │ Oct │ Dec │ Hex │
├─────┼─────┼─────┼─────┤
│ 10  │ 10  │ 10  │ 10  │
└─────┴─────┴─────┴─────┘
sqlite> select horner('1f600');
Parse error: wrong number of arguments to function horner()
  select horner('1f600');
         ^--- error here
sqlite> select horner('1f600',16);
┌────────────────────┐
│ horner('1f600',16) │
├────────────────────┤
│ 128512             │
└────────────────────┘
sqlite>

What you are looking for I required years ago already and realized it as a user defined extension function. I herewith grant my following code for free use (MIT license). Do good and not evil.

/*
** The Horner SQL function returns the number of symbols (1st) to the given base (2nd)
*/
static void hornerFunc(
  sqlite3_context *context, int argc, sqlite3_value **argv )
{
  sqlite_uint64 sum=0;
  if( sqlite3_value_type(argv[0])!=SQLITE_NULL )
  {
	const  char *sz, *pText = sqlite3_value_text(argv[0]);
	int digit, base = sqlite3_value_int(argv[1]);
	for( sz=pText; *sz != '\x0'; sz++ )
	{
	  sum *= base;
	  digit = (*sz - (*sz<='9'?'0':(*sz<='Z'?('A'-10):((base<=26)?('a'-10):('a'-36)))));  // ready for base 62
	  sum += digit;
	}
  }

  sqlite3_result_int64(context, sum );
}

...

sqlite3_create_function_v2(db, "horner",    2, SQLITE_UTF8 | SQLITE_DETERMINISTIC, 0, hornerFunc, 0, 0, NULL);

Yup - that would cover it (and more!) - so the request for ENH would be the consideration of including something like this in the base-product.

This is actually a standard C library function called strtoll.

static void _strtoll_(sqlite3_context *ctx, int argc, sqlite3_value **argv)
{
    sqlite_int64 result;
    int base;
    char* start, stop;

    if (sqlite3_value_type(argv[0]) != SQLITE_TEXT)
        return;
    start = sqlite3_value_text(argv[0]);
    if (*start == 0)
        return;
    base = sqlite3_value_int(argv[1]);
    if (base<0 || base>36)
        return;
    result = strtoll(start, &stop, base);
    if (start == stop)
        return;
    sqlite3_result_int64(ctx, result);
}

    sqlite3_create_function(db, "strtoll", 2, SQLITE_UTF8|SQLITE_DETERMINISTIC|SQLITE_INNOCUOUS, 0, _strtoll_, 0, 0);

I have the same Unicode use-case as OP. I adapted Keith's strtoll idea and made the following. It's the first time I've written much real C (I used to write some HolyC and Zig), so if anyone has any pointers, please let me know ;)

/*
** 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;
}

Did you try it? It looks like it should work. I don't see any obvious errors.

I did try it and it works well, though I'd like to add a few property tests today. Thanks for the feedback.

The system for adding extensions was easy to use as a C beginner, the only confusing issue I had was that #include <sqlite3ext.h> would instantly segfault whereas #include "sqlite3ext.h" would be fine. I'm not quite sure what that was about as otool shows the same libsqlite version:

/usr/lib/libsqlite3.dylib (compatibility version 9.0.0, current version 357.0.0)

I'm new to C so I must be missing something. In any case, it's working now, and I learned some lldb in the meantime.

Relevant / related discussion:

SQLite3 convert hex string to int

https://gist.github.com/ChiChou/97a53caa2c0b49c1991e