Skip to content

Instantly share code, notes, and snippets.

@ChiChou
Last active October 11, 2023 05:44
Show Gist options
  • Save ChiChou/97a53caa2c0b49c1991e to your computer and use it in GitHub Desktop.
Save ChiChou/97a53caa2c0b49c1991e to your computer and use it in GitHub Desktop.
SQLite3 convert hex string to int (requires sqlite >= 3.8.3)
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;
@A60AB5450353F40E
Copy link

@DatArc
Copy link

DatArc commented May 16, 2023

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 !

@felipelalli
Copy link

felipelalli commented Oct 11, 2023

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

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment