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;
@d33tah
Copy link

d33tah commented Jan 13, 2017

Thanks! I hadn't thought of this use of instr() function.

@adius
Copy link

adius commented Apr 1, 2017

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?

@Keven0903
Copy link

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

@Codereamp
Copy link

Codereamp commented Sep 7, 2022

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;

@A60AB5450353F40E
Copy link

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

@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