In SQLITE, How can I convert a hex value stored as a varchar in an integer column to it's integ-CodePudding

Hi: I'm working with a SQlITE (v 3.14.2) in linux. Apparently, sqlite allows users to store char strings in integer columns (I find this shocking, but that's what it apparently allows). Some of those values appear to be hex expressions of an integer. I need to clean this up in a ".dump" (with .mode insert). Here's an example of the odd integer/varchar behavior...

sqlite> .mode insert
sqlite> create table foo (col1 integer);
sqlite> insert into foo (col1) values (1),('2'),(0x3),('0x4'),(0xf),('My good dog Moby');
sqlite> select * from foo;
INSERT INTO table(col1) VALUES(1);
INSERT INTO table(col1) VALUES(2);
INSERT INTO table(col1) VALUES(3);
INSERT INTO table(col1) VALUES('0x4');
INSERT INTO table(col1) VALUES(15);
INSERT INTO table(col1) VALUES('My good dog Moby');

If the inserted value is an int, it gets interpreted as an int, even if it'sinside single quotes. That's fine, other DBs do this too. If the value is a hex value and lacks the single quotes, that gets interpreted correctly too. So far, so good. But if the hex value is inside the single qotes, no good, it apparently gets stored as a string of some sort.

Without necessarily knowing which columns of which tables are integers that need special treatment, is there a way to get the select command to interpret hex values that were inserted with single quotes as ints (just the way it interpreted '2' as 2 above) ?

If it helps at all, I'm actually going to be using .dump, not select, when looking at the data.

Thanks for any help !

CodePudding user response：

SQLite doesn't convert from hex to dec, you need to write such a function yourself. An example can be found in the SQLite Forum:

/*
** Function UNHEX(arg) -> blob
**
** Decodes the arg which must be an even number of hexidecimal characters into a blob and returns the blob
**
*/

#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 _unhexFunc(sqlite3_context *context, int argc, sqlite3_value **argv)
{
    long olength = sqlite3_value_bytes(argv[0]);
    long length;
    unsigned char* data = (unsigned char*)sqlite3_value_text(argv[0]);
    unsigned char* blob;
    unsigned char* stuff;
    unsigned char buffer[4] = {0};

    if ((olength % 2 != 0) || (olength < 2))
    {
        return;
    }

    blob = malloc(length / 2);
    stuff = blob;
    length = olength;

    while (length > 0)
    {
        memcpy(buffer, data, 2);
        *stuff = (unsigned char)strtol(buffer, NULL, 16);
        stuff  ;
        data  = 2;
        length -= 2;
    }
    sqlite3_result_blob(context, blob, olength/2, SQLITE_TRANSIENT);
    free(blob);
}

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

    return sqlite3_create_function(db, "UNHEX", 1, SQLITE_UTF8|SQLITE_DETERMINISTIC|SQLITE_INNOCUOUS, 0, _unhexFunc,  0, 0);
}

#ifdef __cplusplus
}
#endif

You can then call it on the value if it starts with 0x:

SELECT CASE
    WHEN col1 LIKE '0x%'
        THEN UNHEX(col1)
    ELSE
        col1
    END
FROM foo;

Much better way is to fix this in the application that stores the values.

CodePudding user response：

You can actually convert from a string holding a base-16 number to a numeric value in plain Sqlite, but it's kind of ugly, using a recursive CTE:

WITH RECURSIVE
  hexnumbers(hexstr) AS (VALUES ('0x123'), ('0x4'), ('0x7f')),
  parse_hex(num, hexstr, digit, remaining) AS
    (SELECT 0, upper(hexstr), 3, length(hexstr) - 2 FROM hexnumbers
    UNION ALL
     SELECT (num * 16)
            CASE substr(hexstr, digit, 1)
              WHEN '0' THEN 0
              WHEN '1' THEN 1
              WHEN '2' THEN 2
              WHEN '3' THEN 3
              WHEN '4' THEN 4
              WHEN '5' THEN 5
              WHEN '6' THEN 6
              WHEN '7' THEN 7
              WHEN '8' THEN 8
              WHEN '9' THEN 9
              WHEN 'A' THEN 0xA
              WHEN 'B' THEN 0xB
              WHEN 'C' THEN 0xC
              WHEN 'D' THEN 0xD
              WHEN 'E' THEN 0xE
              WHEN 'F' THEN 0xF
            END, hexstr, digit   1, remaining - 1
      FROM parse_hex
      WHERE remaining > 0)
SELECT hexstr, num FROM parse_hex WHERE remaining = 0;

gives

hexstr  num
------  ---
0X4     4
0X7F    127
0X123   291

If you want to go the C function route, your implementation function should look something like:

 void hex_to_dec(sqlite3_context *context, int argc, sqlite3_value **argv) {
    int vtype = sqlite3_value_numeric_type(argv[0]);
    if (vtype == SQLITE_TEXT)
        const char *str = sqlite3_value_text(argv[0]);
        if (str && *str) {
            sqlite3_result_int64(context, strtol(str, NULL, 16));
        }
    } else if (vtype == SQLITE_INTEGER) {
         sqlite3_result_value(context, argv[0]);
    }
}

except maybe with more error checking. The one in @choroba's answer creates a blob, not an integer, which isn't want you want.