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;

I needed to convert a hex encoding with reverse byte order into integer, here's a reverse byte version:

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;

result: 3735928559

I also added this as Q&A on Stackoverflow so it's easier to find https://stackoverflow.com/questions/75592747/how-to-convert-hex-string-into-unsigned-integer-using-sqlite

Hi guys, Fantastic solution. I'm a super noob on SQL so pardon me if the question below is too obvious or too easy to do :
I was wondering if there's a way to process a long (a few millions of rows) table with a 64-bit Ids which string representation is stored in a field of type text ? I couldn't really pass that as a param to the CTE (as start value, instead of 'deadbeef' for example).
I am fully aware that I can write (a too easy) C function to do the job but I want to remain within SQL boundaries.
Thank you !

I have a table with hex timestamps as ids like this:
Id Name
015b2a63ec53 Alice
015b2aa24993 Bob

and I want to create a view which renders it like this:
Creation Date Name
2017-04-01 16:40:43 Alice
2017-04-01 17:48:50 Bob

Your approach is the only one I found which worked to convert the id. However, I wasn't able to create a working view for the whole table as my SQL skills are a little limited 😅

Maybe you can help me @ChiChou?

Utilizing ChatGPT-4 alongside this ChiChou snippet, I achieved a result akin to the following:

WITH 
    hexchars(key, value) AS (
        VALUES
            ('0', 0),('1', 1),('2', 2),('3', 3),('4', 4),('5', 5),('6', 6),('7', 7),('8', 8),('9', 9),
            ('A', 10),('B', 11),('C', 12),('D', 13),('E', 14),('F', 15),
            ('a', 10),('b', 11),('c', 12),('d', 13),('e', 14),('f', 15)
    ),
    -- Get all UUID from table
    all_uuids AS (
        SELECT uuid, replace(substr(uuid, 0, 14), '-', '') as hex_timestamp FROM podcasts_tts
    )
    -- Processing each UUID
SELECT
    uu.uuid,
    (
        WITH RECURSIVE
            -- Converting the part of timestamp hex to decimal
            unhex(str, val, weight) AS (
                SELECT uu.hex_timestamp, 0, 1
                    UNION ALL
                SELECT 
                    substr(str, 1, length(str) - 1),
                    val + (select value from hexchars where key = substr(str, length(str), 1)) * weight,
                    weight * 16
                FROM unhex WHERE length(str) > 0
            )
        SELECT val FROM unhex ORDER BY weight DESC LIMIT 1
    ) AS decimal_timestamp
FROM
    all_uuids uu;

It's a monster, but it works.

In my case, I have a column UUID v7 with has a timestamp inside, so I need to extract it with replace(substr(uuid, 0, 14), '-', '')