Microsoft SQL Server forum - Select where uniqueidentifier = 0x hex?

Author

Select where uniqueidentifier = 0x hex?

turnstyle

2005-08-22, 1:23 pm

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 = 0x111111112222333344
44555555555555

Any tips?

thanks, -Scott

Chandra

2005-08-22, 8:23 pm

just try and convert the datatype to varbinary

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

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

turnstyle

2005-08-22, 8:23 pm

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

2005-08-22, 8:23 pm

turnstyle (scott@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 = 0x111111112222333344
44555555555555

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 = 0x19D3AD35A5FE3B4486
280FD8588BE6DA
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, esquel@sommarskog.se

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

turnstyle

2005-08-23, 9:23 am

> 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

2005-08-23, 11:24 am

turnstyle (scott@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, esquel@sommarskog.se

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

Sponsored Links