SQLite how to convert BLOB byte to INT with built-in functions?

Question

i have an sqlite database that contains a column with BLOB data.
these BLOB data are 4 byte width. i want to split the 4 bytes appart and convert each part to an integer value to calculate with it.
i found out, that i can use SUBSTR(val, start, length) to take the BLOB value appart. the result is still of type BLOB.
but how can i convert the BLOB/byte to an integer value?
is there a built-in function that can convert byte BLOB values to an integer?
or is there a way to convert a hex-string-value into an integer value, so i could play with HEX(val) or 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

There is no built in function that I know of, so this is how I do it - if you know how many bytes you want to convert:

--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 2

@rayzinnz, thank you for the hint.

in the meantime i gave up. i puzzled together a kind of a solution, but i never got it work to set the initial x'cafe1a7e' value from outside the WITH RECURSIVE construction.

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

the BLOB value there is hardcoded. maybe you find a way.

Answer 3

You can convert one hex digit at a time using 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))

Example output:

D91F8E91|3642723985|D91F8E91

(Simplification of idea from [1].)