How to Convert Hex String Into Unsigned Integer Using SQLite

Question

I have some string fields that store hexadecimal representation of unsigned integers in reverse byte order. I'd like to convert it back to integers.

For example the string deadbeef must result in number 4022250974.

Answer 1

I found an answer for normal byte order here:

WITH RECURSIVE
  unhex(str, val, weight) AS (
    SELECT 'deadbeef', 0, 1
      UNION ALL
        SELECT 
          substr(str, 1, length(str) - 1),
          val + (instr('0123456789abcdef', substr(str, length(str), 1)) - 1) * weight,
          weight * 16
        FROM unhex WHERE length(str) > 0
  )
  SELECT val FROM unhex order by weight desc limit 1;

and I then adapted it to do the same for reverse byte order:

WITH RECURSIVE unhex(str, val, weight) AS (
  SELECT 
    'efbeadde', 0, 1
  UNION ALL 
  SELECT 
    substr(str, 3), 
    val + (instr('0123456789abcdef', substr(str, 2, 1)) - 1) * weight + (instr('0123456789abcdef', substr(str, 1, 1)) - 1) * weight * 16, 
    weight * 256 
  FROM unhex WHERE length(str) > 0
)
SELECT val FROM unhex order by weight desc limit 1;