SQLite Forum

To and From Hex Conversion Methods

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[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;
}
}
``````

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.