SQLite Forum

To and From Hex Conversion Methods
Login

To and From Hex Conversion Methods

(1) By Stephen C (StephenC) on 2020-03-18 18:30:19 [link] [source]

I'm attempting to figure out how to take a 12-digit hex based string and do some maths on particular portions of it. (It's for a game, and the developers have done some HEX based %/MOD math on converting from one coordinate system to another).

If I manually enter the numbers as such:

select printf('%0.4X',(printf('%d',0xFFF)+printf('%d',0x7FF))%0x1000)

I get the expected answer of 0x7FE.

FFF is the text i extract out of a longer string (substr(:Add12,10,3)), the 0x7FF and 0x1000 are considered constants.

How can I formulate my SQL statement to take a string based FFF and convert it to a Hex value?

The things I've tried are
- 0x||'FFF' which fails
- '0xFFF' which gives me 0
- FFF gives me an invalid field
- 'FFF' gives me 0

I thought about just taking that 12-digit HEX string, convert it to a digit and play with ANDs and SHIFTs, but, the problem is still taking that HEX string and convert it to a number easily.

(2) By Keith Medcalf (kmedcalf) on 2020-03-18 20:22:01 in reply to 1 [link] [source]

There is no builtin to convert a hex string to a number.  You could write your own extension function or a CTE to do that.

Example extension function:

/*
** Convert Text Argument which is hex string into decimal
*/

static void _FromHex(sqlite3_context* context, int argc, sqlite3_value **argv)
{
    const char * src;
    unsigned long long value = 0;

    if (sqlite3_value_type(argv[0]) == SQLITE_TEXT)
    {
        src = sqlite3_value_text(argv[0]);
        if (sqlite3_value_bytes(argv[0]) > 16)
            return;
        while (*src != 0)
        {
            if (!isxdigit(*src))
                return;
            value = value << 4;
            value += (*src > '9' ? ((*src & 0x0f) + 9) : (*src & 0x0f));
            src++;
        }
        sqlite3_result_int64(context, (sqlite_int64)value);
    }
}

(3) By Ibrahim Onaran (onaran) on 2021-02-04 08:35:40 in reply to 2 [link] [source]

I have using your function compiled by CYGWIN, however the isxdigit crashes program. I modified the code so that it solves this problem, and also adds bigendian option.

/*
** given a hex string converts it to int
*/
static void hex2intFunc(sqlite3_context* context, int argc, sqlite3_value** argv)
{
	const char* src;
	uint64_t value = 0;
	uint64_t nextChar = 0;
	int nShift = 0;
	assert(argc == 2);
	if (SQLITE_NULL == sqlite3_value_type(argv[0]))
	{
		sqlite3_result_null(context);
		return;
	}
	bool bBigEndian = sqlite3_value_int64(argv[1]);
	//if ( bBigEndian & ~0x1UL)
	//{
	//	sqlite3_result_error(context, "The second argument should be 0 (little endian) or 1 (big endian)", -1);
	//	return;
	//}

	if (sqlite3_value_type(argv[0]) == SQLITE_TEXT)
	{
		src = (char*)sqlite3_value_text(argv[0]);
		if (strlen(src) > 16)
		{
			// sqlite3_result_int64(context, (sqlite_int64)value);
			return;
		}
		while (*src != 0)
		{
			switch (src[0])
			{
			case 'A'...'F':
				nextChar |= (uint64_t)src[0] - 'A' + 0x0A;
				break;
			case 'a'...'f':
				nextChar |= (uint64_t)src[0] - 'a' + 0x0A;
				break;
			case '0'...'9':
				nextChar |= (uint64_t)src[0] - '0';
				break;
			default:
				nextChar = 0x100;
			}
			if (nextChar & 0x100)
			{
				value = 0;
				nextChar = 0;
				break;
			}
			if (bBigEndian)
			{
				nShift++;
				if (!(nShift & 0x01))
				{
					nextChar <<= ((nShift - 2)<<2);
					value |= nextChar;
					nextChar = 0;
				}
				else
					nextChar <<= 4;
			}
			else
			{
				value = value << 4;
				value |= nextChar;
				nextChar = 0;
			}
			sqliteNextChar(src);
		}
		if (nextChar)
		{
			nextChar <<= ((nShift -1) << 2);
			value |= nextChar;
		}
		sqlite3_result_int64(context, (sqlite_int64)value);
		// break;
	}
}

(4) By Purexo on 2021-07-05 15:33:45 in reply to 1 [source]

Hello,

To exercice me on sqlite I made a project with sqlite and technical doc unicode. The target is to have a database with all unicode codepoint simple to query.

So I wanted to parse codepoint : encoded in a string, integer in hexadecimal encoding. I search builtin solution to parse hexadecimal string number into INTEGER but it seem it's impossible with builtin tools.

I tried https://sqlite.org/forum/forumpost/dd104572f2833e48?t=h but I dont need unhex from hex blob data so it didn't work well.

Next I tried with app function with better-sqlite3 npm module (Node.js).

function parseIntFromHexaStr(hexastr) {
    return parseInt(hexastr, 16);
}
db.function('parse_int_from_hexa_str', { varargs: false, deterministic: true }, parseIntFromHexaStr);
And it work well.

But I prefer my project be sqlite only, so I write a sqlite extension :

// sqlstrtol.c
/*
** Function STRTOL(str, base=10) -> INTEGER
**
** Decodes the str which must be a parsable number by strtol with the base you gave, if no base passed as argument, it will be 10.
*/

#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 _strtolFunc(sqlite3_context *context, int argc, sqlite3_value **argv)
{
    char* text;
    int base;
    char* end;

    // check if arguments are between 1 and 2
    if (argc < 1 || argc > 2) {
        sqlite3_result_null(context);
        return;
    }

    // init variables
    text = (char*)sqlite3_value_text(argv[0]);
    if (argc == 2) {
        base = sqlite3_value_int(argv[1]);
    }
    else {
        base = 10;
    }
    
    long value = strtol(text, &end, base); 

    // parse error strtol put text to endPtr
    if (end == text) {
        sqlite3_result_null(context);
        return;
    }

    sqlite3_result_int64(context, value);
}

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

    return sqlite3_create_function_v2(db, "STRTOL", 2, SQLITE_UTF8|SQLITE_DETERMINISTIC|SQLITE_INNOCUOUS, 0, _strtolFunc, 0, 0, 0);
}

#ifdef __cplusplus
}
#endif

So I exposed strtol from stdlib https://en.wikibooks.org/wiki/C_Programming/stdlib.h/strtol

I'm not a C developper, so I don't know if it's the best way to parse string to integer with C.

If an active sqlite maintainer pass here : are you opposed to add this kind of function in builtins of sqlite ? If someone make a push-request to add this (a parseInt method based on strtol or similar) in builtins, is it ok for you ?

I'm curious about C, fossil and sqlite. So if you have time to help me to make a quality push-request for sqlite, I would love to do it.