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 [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 [link] [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.