Skip to content

Instantly share code, notes, and snippets.

@ChiChou ChiChou/unhex.sql
Last active Jan 3, 2019

Embed
What would you like to do?
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

This comment has been minimized.

Copy link

commented Jan 13, 2017

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

@adius

This comment has been minimized.

Copy link

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

This comment has been minimized.

Copy link

commented Aug 2, 2017

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

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
You can’t perform that action at this time.