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

大牛 请问你知道安卓微信数据库如何解密成标准库呢，知道密码的情况下，不是说复制到新库，是数据库文件原始数据解密，大牛 有研究过吗

Thanks, the post is old, but your suggestion keeps coming early in results when googling [ sqlite hex to number ]

Let me suggest a modified version allowing mixing latin chars case (fff, FFF) and (implicit result) returning NULL when invalid characters are in the input string, so 'livebeef' returns NULL :)

WITH RECURSIVE
  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)
  ),
  unhex(str, val, weight) AS (
    SELECT 'deadbeef', 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;

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), '-', '')