Opposite of HEX() in SQLite?

Question

I have this simple query that returns a bunch of guids as hexadecimal strings:

SELECT HEX(guid) FROM table;

One of them is for instance 43F4124307108902B7A919F4D4D0770D. Then imagine I want to get the record with this guid, so I write a query like this:

SELECT * FROM table WHERE guid = '43F4124307108902B7A919F4D4D0770D';

Of course, this will not work, since the string is directly interpreted as a blob and not converted to it's hex value. I looked here, but couldn't find anything that looks like a method that takes a hexadecimal string and converts it to a blob.

why don't you use `SELECT * FROM table WHERE guid = 'HEX(43F4124307108902B7A919F4D4D0770D)'`? — fnc12, Jan 01 '20 at 09:45

score 36 · Accepted Answer · answered Nov 05 '13 at 08:21

36

While writing the question I found the answer. I simply had to add an X before the string. Like this:

SELECT * FROM table WHERE guid = X'43F4124307108902B7A919F4D4D0770D';

I figured I should post the question anyway, since non of the "Similar Questions" answers this. What I was looking for was not a function, but a literal and when I realized this I quickly found the answer here.

answered Nov 05 '13 at 08:21

Erik B

40,889
25
119
135

1

http://stackoverflow.com/questions/1039461/how-to-display-blob-value-using-xabc-binary-string-literal-syntax answered your question indirectly. – ddevienne Jul 23 '14 at 12:35

score 0 · Answer 2 · answered Feb 12 '23 at 14:45

In the upcoming version it's supported:

SQLite version 3.41.0 2023-02-08 12:47:37

Enter ".help" for usage hints.

Connected to a transient in-memory database.

Use ".open FILENAME" to reopen on a persistent database.

sqlite> select unhex('41') ...> ;

A

sqlite> select hex('a') ...> ;

61

sqlite> .q

Opposite of HEX() in SQLite?

2 Answers2

Linked