To and From Hex Conversion Methods
(1) By Stephen C (StephenC) on 2020-03-18 18:30:19 [link]
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]
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
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. ```c /* ** 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]
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). ```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 : ```c // 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.