I have a SQLite table with a column in it that stores a GUID as a byte array
I am trying to get the a Guid string out of my SQL query. So far I have tried:
Select BlobGuidColumn from [MyTable]
But this returns a blob column
Is it possible to return a Hex string from a SQL query?
There are a few issues with the accepted answer, but the most important one is that it does not always generate the correct results. If the goal is simply to map 128 bits from binary to a GUID-like (8-4-4-4-12) representation, it'll do. But if you actually want something evaluates to the same GUID, it won't do as the endianness of the underlying data is not taken into account.
Take the following guid as a concrete example:
BC7CAE8C-E4D7-49CA-86E4-5FD540106CC0
Represented as an array of bytes, you don't necessarily get
BC-7C-AE-8C-E4-D7-49-CA-86-E4-5F-D5-40-10-6C-C0
as the answer, as the internal representation of the GUID/UUID is byte order dependent depending on the variant of the UUID in question. Variant 1 UUIDs are strictly network byte order (little endian), while Variant 2 UUIDs are identical in every respect, except that half the groupings are stored in memory (or on disk in the database) in big endian byte order, while the other half is in network byte order.
I've written a lengthy article on the matter that delves into the history of UUID/GUID and the differences between the two variants, as well as provides instructions on converting a UUID from binary to text: Converting a binary/blob guid column to text in SQL: it's a lot harder than you think.
Depending on the variant in question, you can either directly use the SQL HEX() function as in the currently accepted answer:
SELECT substr(hex(guid), 1, 8)
|| '-' || substr(hex(guid), 9, 4)
|| '-' || substr(hex(guid), 13, 4)
|| '-' || substr(hex(guid), 17, 4)
|| '-' || substr(hex(guid), 21, 12)
FROM [MyTable]
Or, if the source data comes from the Microsoft world (such as a COM/WIN32/.NET application), you would need to use this far more convoluted approach instead, which takes the appropriate byte order into account for each grouping:
SELECT
substr(hguid, 7, 2) || substr(hguid, 5, 2) || substr(hguid, 3, 2) || substr(hguid, 1, 2) || '-'
|| substr(hguid, 11, 2) || substr(hguid, 9, 2) || '-'
|| substr(hguid, 15, 2) || substr(hguid, 13, 2) || '-'
|| substr(hguid, 17, 4) || '-'
|| substr(hguid, 21, 12)
AS guid
FROM (SELECT hex(guid) AS hguid FROM messages)
(Here the guid column has been converted to hexadecimal just once on the last line to avoid the overhead of doing so repeatedly for each byte.)
I'm actually not clear on whether or not (as in the OP's particular scenario) Microsoft SQL Server stores GUIDs on-disk in network byte order or as (partially) big endian, though I would tend to think it's the latter given that it's universally the case everywhere else when it comes to Microsoft technologies (it's not a universal rule, but by and large, "guid" implies Little Endian whereas "uuid" implies Big -- see the article for more on this).
Hint: if the results start with zeros, you're doing it wrong. Given the source of the first group in a UUIDv1/UUIDv2 (timestamp), a lot of sources with only limited resolution have zeroes at the end of the first grouping of hexadecimal digits. But when converted incorrectly, that group would end up in the beginning: a surefire sign that you need to use the alternative answer in your particular case.
Using information from this question: Sqlite: How to cast(data as TEXT) for BLOB and this question: Convert varchar to uniqueidentifier in SQL Server
I got the answer:
SELECT substr(hex(BlobGuidColumn), 1, 8) || '-' || substr(hex(BlobGuidColumn), 9, 4) || '-' || substr(hex(BlobGuidColumn), 13, 4) || '-' || substr(hex(BlobGuidColumn), 17, 4) || '-' || substr(hex(BlobGuidColumn), 21, 12) FROM [MyTable]
For me
SELECT hex(yourColumnWithBlobs) FROM yourTable
did work perfectly.
Of course this line will only show you the corresponding column but the query can be customized to your usecase.
