CODEWITHSUNDEEP
sqlitehex
Erik B
Erik B

Reputation: 42584

Opposite of HEX() in SQLite?

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.

Upvotes: 22

Views: 12324

Answers (2)

Klaas-Z4us-V
Klaas-Z4us-V

Reputation: 327

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

Upvotes: 1

Erik B
Erik B

Reputation: 42584

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.

Upvotes: 37

Related Questions