0

I want to join two tables on a UUID. table A's UUID is represented as varchar(32). table B's UUID is represented as binary(16).

what's the best way to join a varchar to a binary column?

I've tried using some sybase functions for this, but I'm getting different results and unsure of why:

select hextobigint('0x000036ca4c4c11d88b8dcd1344cdb512')
3948051912944290701
select convert(bigint,0x000036ca4c4c11d88b8dcd1344cdb512)
-2877434794219274240

what am I missing about convert and hextobigint? I must be misundstanding at least one of these functions. thanks for your help!

Paul Sanwald
  • 10,899
  • 6
  • 44
  • 59
  • I don't know anything about sybase or these functions specifically, but... for performance reasons, and the health of your relationship with the DBAs, pick a datatype, and stick to it. Seriously, don't have two (or more) different types of `Id`s (UUID or whatever) that you expect to join on - having to do a conversion for every comparison is annoying and performance-degrading. – Clockwork-Muse Nov 11 '11 at 19:50
  • I agree 100%, this is not a database I designed or have any control over. I'm just trying to query it (it's actually a join across 2 schemas) – Paul Sanwald Nov 11 '11 at 20:13
  • In that case... are you able to get the hex as the `char` representation, or convert the text string to the binary? – Clockwork-Muse Nov 11 '11 at 21:14

2 Answers2

1

got it eventually with the help of some colleagues:

select strtobin(convert(char(32), '000036ca4c4c11d88b8dcd1344cdb512'))
Paul Sanwald
  • 10,899
  • 6
  • 44
  • 59
0

The problem is on the select convert(bigint,0x000036ca4c4c11d88b8dcd1344cdb512).

If you remove the first zeros it'll give you the same result:

select hextobigint('0x000036ca4c4c11d88b8dcd1344cdb512')

and

select convert(bigint,0x36ca4c4c11d88b8dcd1344cdb512)
aF.
  • 64,980
  • 43
  • 135
  • 198