Select where uniqueidentifier = 0x hex?

[turnstyle]

Hi all,

I'm trying to run a select where a
uniqueidentifier/GUID equals a hex, but I don't seem to be getting
matches.

For example, this query returns the expected record:
select * from items where itemGUID =
'{11111111-2222-3333-4444-555555555555}'

But this one does not:
select * from items where itemGUID = 0x11111111222233334444555555555555

Any tips?

thanks, -Scott

[Chandra]

just try and convert the datatype to varbinary

best Regards,
Chandra
http://www.SQLResource.com/
http://chanduas.blogspot.com/
---------------------------------------

*** Sent via Developersdex http://www.developersdex.com ***

[turnstyle]

Do you know if it is possible to do a 0x style lookup from a
uniqueidentifier column?

My understanding was that uniqueidentifier is basically a binary(16) --
and so I thought I could use both {GUID} and 0x to perform a lookup...

thanks, -Scott

[Erland Sommarskog]

turnstyle (sc***@turnstyle.com) writes:

I'm trying to run a select where a
uniqueidentifier/GUID equals a hex, but I don't seem to be getting
matches.

For example, this query returns the expected record:
select * from items where itemGUID =
'{11111111-2222-3333-4444-555555555555}'

But this one does not:
select * from items where itemGUID = 0x11111111222233334444555555555555

This is because the bytes are rearranged in the textual representation.
Here is an example that works:

CREATE TABLE t (guid uniqueidentifier NOT NULL)
INSERT t (guid) VALUES ('35ADD319-FEA5-443B-8628-0FD8588BE6DA')
SELECT * FROM t
SELECT * FROM t WHERE guid = 0x19D3AD35A5FE3B4486280FD8588BE6DA
go
DROP TABLE t
That is, in the first three groups, the byte order should be reversed.
So, yes, you can do it. Question is only: should you?
--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp

[turnstyle]

> the bytes are rearranged in the textual representation.

ugh, that seems like a pretty odd design (and I'll bet I'm not the
first to say so.. ;)

Just curious -- is there a general opinion regarding whether it's
better to store GUIDs in a uniqueidentifier or a binary(16)?

The binary(16) seems to return a binary result whereas the
uniqueidentifier seems to return a string of the hex equivalent, and
what I need is that string -- is there a simple way to select a
binary(16) and get the hex string back?

thanks, -Scott

[Erland Sommarskog]

turnstyle (sc***@turnstyle.com) writes:

Just curious -- is there a general opinion regarding whether it's
better to store GUIDs in a uniqueidentifier or a binary(16)?
I don't know if there is a general opinion, but personally I can see
no point in stored a GUID as a binary(16) where there is a built-in
data type.
The binary(16) seems to return a binary result whereas the
uniqueidentifier seems to return a string of the hex equivalent, and
what I need is that string -- is there a simple way to select a
binary(16) and get the hex string back?

Depends on what you mean with simple. But since there is no built-in
documented function for writing a binary value as a hex string into a
character value, and you also need to use substring(), I would say that
you would only do this if you like to hurt yourself.
--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp