SQLite3 convert hex string to int (requires sqlite >= 3.8.3)

unhex.sql

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;

It's as easy as hexstr->>'$' using the JSON features introduced by version 3.38.0 (2022-02-22).

See detailed description on Stack Overflow