SQLite 3.27

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

CREATE TABLE tablename (col BLOB);
✓
INSERT INTO tablename (col) VALUES
  (X'000D001801C70017FD15'), -- 13,24,455,23,64789 
  (X'006405DC00482710270F'); -- 100, 1500, 72, 10000, 9999
✓
SELECT hex(col) col FROM tablename;


col


000D001801C70017FD15

006405DC00482710270F


WITH cte(id) AS (VALUES (1), (2), (3), (4), (5))
SELECT HEX(t.col) hex_col, 
       c.id,
       SUBSTR(HEX(t.col), (c.id - 1) * 4 + 1, 4) number
FROM tablename t JOIN cte c
ORDER BY t.rowid, c.id;


hex_col
id
number


000D001801C70017FD15
1
000D

000D001801C70017FD15
2
0018

000D001801C70017FD15
3
01C7

000D001801C70017FD15
4
0017

000D001801C70017FD15
5
FD15

006405DC00482710270F
1
0064

006405DC00482710270F
2
05DC

006405DC00482710270F
3
0048

006405DC00482710270F
4
2710

006405DC00482710270F
5
270F


WITH cte(id) AS (
  SELECT 1 
  UNION ALL
  SELECT id + 1
  FROM cte
  WHERE id < 5
)
SELECT HEX(t.col) hex_col, 
       c.id,
       SUBSTR(HEX(t.col), (c.id - 1) * 4 + 1, 4) number
FROM tablename t JOIN cte c
ORDER BY t.rowid, c.id;


hex_col
id
number


000D001801C70017FD15
1
000D

000D001801C70017FD15
2
0018

000D001801C70017FD15
3
01C7

000D001801C70017FD15
4
0017

000D001801C70017FD15
5
FD15

006405DC00482710270F
1
0064

006405DC00482710270F
2
05DC

006405DC00482710270F
3
0048

006405DC00482710270F
4
2710

006405DC00482710270F
5
270F

hex_col	id	number
000D001801C70017FD15	1	000D
000D001801C70017FD15	2	0018
000D001801C70017FD15	3	01C7
000D001801C70017FD15	4	0017
000D001801C70017FD15	5	FD15
006405DC00482710270F	1	0064
006405DC00482710270F	2	05DC
006405DC00482710270F	3	0048
006405DC00482710270F	4	2710
006405DC00482710270F	5	270F