在sqlite中，如何将存储为整数列中的VARCHAR存储的十六进制值转换为整数等效？

Question

嗨：我正在Linux中使用SQLite（V 3.14.2）。显然，SQLITE允许用户将字符串存储在整数列中（我觉得这令人震惊，但这显然允许）。其中一些值似乎是整数的十六进制。我需要在“ .dump”（带有.mode插入）中清理它。这是一个奇数整数/varchar行为的示例...

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');

如果插入值是int，即使它是insInside单引号，它也会被解释为int。很好，其他DB也这样做。如果值是十六进制的值并且缺乏单引号，那也可以正确解释。到目前为止，一切都很好。但是，如果十六进制值在单个QOTES内部，则没有好处，显然将其存储为某种字符串。

不一定知道哪些表是需要特殊处理的整数的列，是否有一种方法可以使SELECT命令解释用单引号插入的HEX值（只是它将“ 2”解释为上述2）？

如果它完全有帮助，我实际上会在查看数据时使用.dump，而不是选择。

感谢您的帮助！

Answer 1

您 can 实际上是从固定基准数字的字符串转换为普通sqlite中的数字值，但是使用a

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;

​

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

---

​

 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]);
    }
}

​@choroba答案中的一个会产生一个斑点，而不是整数，而不是想要您想要的。

Answer 2

SQLite不会从十六进制转换为DEC，您需要自己编写这样的功能。 https://sqlite.org/forum/info/79dc039e21c6a1ea”中找到一个示例

/*
** 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

可以在 0x ：

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

更好的方法是将其修复在存储值的应用程序中。