SQLite如何使用内置函数将BLOB字节转换为INT

Question

我有一个sqlite数据库，其中包含一列BLOB数据
这些BLOB数据的宽度为4字节。我想将4个字节的appert拆分，并将每个部分转换为一个整数值进行计算。
我发现，我可以使用SUBSTR(val, start, length)来获取BLOB值appert。结果仍然是BLOB类型
但是如何将BLOB/字节转换为整数值
是否有一个内置函数可以将字节BLOB值转换为整数
或者有没有办法将十六进制字符串值转换为整数值，这样我就可以玩HEX(val)或QUOTE(val)了

CREATE TEMP TABLE IF NOT EXISTS test AS SELECT x'cafe1a7e' AS val;
SELECT (val)
, TYPEOF(val)
, HEX(val)
, QUOTE(val)
, TYPEOF(HEX(val))
, TYPEOF(QUOTE(val))
, CAST(val AS INT)
, CAST(HEX(val) AS INT)
, CAST(QUOTE(val) AS INT)
, SUBSTR(val, 1, 1)
, TYPEOF(SUBSTR(val, 1, 1))
, HEX(SUBSTR(val, 1, 1))
, HEX(SUBSTR(val, 2, 1))
, HEX(SUBSTR(val, 3, 2))
, val + val
, SUBSTR(val, 1, 1) + 1
, CAST(SUBSTR(val, 1, 1) AS INT)
FROM test;
DROP TABLE test;

Answer 1

您可以使用instr:一次转换一个十六进制数字

SELECT hex(b), n, printf("%04X", n)
FROM (SELECT b,
(instr("123456789ABCDEF", substr(hex(b), -1, 1)) << 0) |
(instr("123456789ABCDEF", substr(hex(b), -2, 1)) << 4) |
(instr("123456789ABCDEF", substr(hex(b), -3, 1)) << 8) |
(instr("123456789ABCDEF", substr(hex(b), -4, 1)) << 12) |
(instr("123456789ABCDEF", substr(hex(b), -5, 1)) << 16) |
(instr("123456789ABCDEF", substr(hex(b), -6, 1)) << 20) |
(instr("123456789ABCDEF", substr(hex(b), -7, 1)) << 24) |
(instr("123456789ABCDEF", substr(hex(b), -8, 1)) << 28) AS n
FROM (SELECT randomblob(4) AS b))

示例输出：

D91F8E91|3642723985|D91F8E91

(对[1]的想法进行了简化。(

Answer 2

据我所知，没有内置函数，所以我就是这样做的——如果你知道你想转换多少字节：

--creates table h2i with numbers 0 to 255 in hex and int
CREATE TEMP TABLE bits (bit INTEGER PRIMARY KEY);INSERT INTO bits VALUES (0);INSERT INTO bits VALUES (1);
CREATE TEMP TABLE h2i (h TEXT, i INT);
INSERT INTO h2i (h, i) SELECT printf('%02X',num),num FROM (SELECT b7.bit * 128 + b6.bit * 64 + b5.bit * 32 + b4.bit * 16 + b3.bit * 8 + b2.bit * 4 + b1.bit * 2 + b0.bit AS num FROM bits b7, bits b6, bits b5,bits b4, bits b3, bits b2, bits b1, bits b0) as nums;
SELECT
HEX(SUBSTR(val, 1, 1)),h2i0.i
,HEX(SUBSTR(val, 2, 1)),h2i1.i
,HEX(SUBSTR(val, 3, 2)),h2i2.i*256+h2i3.i
,HEX(SUBSTR(val, 1, 4)),h2i0.i*16777216+h2i1.i*65536+h2i2.i*256+h2i3.i
FROM test
JOIN h2i h2i0 ON h2i0.h=HEX(SUBSTR(val, 1, 1))
JOIN h2i h2i1 ON h2i1.h=HEX(SUBSTR(val, 2, 1))
JOIN h2i h2i2 ON h2i2.h=HEX(SUBSTR(val, 3, 1))
JOIN h2i h2i3 ON h2i3.h=HEX(SUBSTR(val, 4, 1))
;

Answer 3

@rayzinnz，感谢您的提示。

与此同时，我放弃了。我想出了一种解决方案，但我从未从WITH RECURSIVE构造之外设置初始x'cafe1a7e'值。

WITH RECURSIVE fx(val_hex, val_int, iter) AS (
VALUES(HEX(x'cafe1a7e'), 0, 0)
UNION ALL
SELECT
SUBSTR(val_hex, 1, LENGTH(val_hex) - 1),
val_int + (
CASE SUBSTR(val_hex, -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 10
WHEN 'B' THEN 11
WHEN 'C' THEN 12
WHEN 'D' THEN 13
WHEN 'E' THEN 14
WHEN 'F' THEN 15
ELSE 0
END << (iter * 4)
),
iter + 1
FROM fx
WHERE val_hex != ''
LIMIT 9
)
--SELECT * FROM fx
SELECT val_int FROM fx WHERE val_hex == ''
;

那里的BLOB值是硬编码的。也许你能找到办法。