Using guid in sqlite select where guid is stored in the sqlite db as binaries

Question

I have a table Employee in the SQLite database. Whose primary key is "ID" of GUID type. I try to find the record with id = guid 'a8828ddf-ef22-4d36-935a-1c66ae86ebb3' with the following query with no luck:

SELECT * FROM Employee
WHERE Employee.Id = 'a8828ddf-ef22-4d36-935a-1c66ae86ebb3'

Can anyone tell me how should I write the query?

Thanks,

Answer 1

This is a old question, but comes up with searches and while the selected answer is correct, it might not work for everyone easily. For example when using .NET bindings to SQLite.

The reason is that when the GUID is in format a8828ddf-ef22-4d36-935a-1c66ae86ebb3 you cannot just remove the dashes and put it to X'...'. The GUID is in several parts and the database is storing the binary differently.

When the GUID is a8828ddf-ef22-4d36-935a-1c66ae86ebb3 it will be stored in little endian as X'df8d82a822ef364d5a93b3eb86ae661c'. Note that all parts are reversed within the string.

Of course if you insert the data directly by removing the dashes as X'a8828ddfef224d36935a1c66ae86ebb3' you can also retrieve it with that, but if you let .NET to handle the conversion, GUID will be stored as little endian byte representations.

(This all assumes little endian architecture, haven't tested on big endian)

Answer 2

The GUID is probably being stored as a binary blob; try:

SELECT * FROM Employee
WHERE Employee.Id = X'a8828ddfef224d36935a1c66ae86ebb3';