1

I have 2 tables - Table A with primary key column of type binary(16) and another table B with foreign key referring to the same column but with data type as varchar(50). So table A has values like 0x0007914BFFEC4603A6900045492EFA1A and table B has the same value stored as 0007914BFFEC4603A6900045492EFA1A.

How do i compare these 2 columns, which would give me 

0007914BFFEC4603A6900045492EFA1A = 0x0007914BFFEC4603A6900045492EFA1A
kristof
  • 52,923
  • 24
  • 87
  • 110
patil.rahulk
  • 574
  • 1
  • 3
  • 13
  • possible duplicate of [SQL Server converting varbinary to string](http://stackoverflow.com/questions/12139073/sql-server-converting-varbinary-to-string) – Paul Williams Aug 11 '14 at 13:41
  • added sql-server-2005 tag, which explains why [Paul's solution](https://stackoverflow.com/a/25244777/3241) did no work – kristof Aug 11 '14 at 14:23

4 Answers4

1

You will need to convert the binary(16) to a string. A sample of how to do this can be found in the question below. This question converts a varbinary to a string, but the same technique can be used for a binary column or variable:

SQL Server converting varbinary to string

Example code for how to do this is below:

declare @bin binary(16), @str varchar(50)
set @bin = 0x0007914BFFEC4603A6900045492EFA1A
set @str = '0007914BFFEC4603A6900045492EFA1A'

select @bin as'binary(16)', @str as 'varchar(50)'

-- the binary value is not equal to the string value
-- this statement returns 'binary value is not equal to string'
if @bin = @str select 'binary value is equal to string'
else select 'binary value is not equal to string'

declare @binstr varchar(50)
select @binstr = convert(varchar(50), @bin, 2)
select @binstr

-- the converted string value matches the other string
-- the result of this statement is "converted string is equal"
if @binstr = @str select 'converted string is equal'
else select 'converted string is NOT equal'

To use this in a join, you can include the conversion in the ON clause of the inner join or in a WHERE clause:

select *
from TableA
inner join TableB
    on TableB.char_fk = convert(varchar(50), TableA.bin_pk, 2)

UPDATE

For SQL Server 2005, you can use an XML approach shown by Peter Larsson here:

-- Prepare value
DECLARE @bin VARBINARY(MAX)
SET     @bin = 0x5BAA61E4C9B93F3F0682250B6CF8331B7EE68FD8

-- Display the results
SELECT @bin AS OriginalValue,
        CAST('' AS XML).value('xs:hexBinary(sql:variable("@bin"))', 'VARCHAR(MAX)') AS ConvertedString

You can also use the undocumented function sys.fn_varbintohexstr, but as this post on dba.stackexchange.com explains, there are several reasons why you should avoid it.

Community
  • 1
  • 1
Paul Williams
  • 16,585
  • 5
  • 47
  • 82
  • This does not work, i need to convert either binary to varchar or vice versa to be abel to equate these 2 - 0x0007914BFFEC4603A6900045492EFA1A and '0007914BFFEC4603A6900045492EFA1A' – patil.rahulk Aug 11 '14 at 13:49
  • Those are the example values I used in my code above. The code converts binary to varchar for comparison. Did the code not work, or do you need something else? – Paul Williams Aug 11 '14 at 13:54
  • I included a sample of how to do this conversion in a join. – Paul Williams Aug 11 '14 at 13:56
  • It does not work, gives a different value. Found the answer on an older post though. need to use master.dbo.fn_varbintohexstr (@source) – patil.rahulk Aug 11 '14 at 13:57
  • 1
    I'm confused. When I run this code, it gives me exactly the same value, and the second comparison returns "converted string is equal". – Paul Williams Aug 11 '14 at 13:58
  • For me it is returning "converted string is NOT equal" – patil.rahulk Aug 11 '14 at 14:01
  • @patil.rahulk - you should have said that you are using sqlserver 2005. Paul's solution works fine from 2008 up – kristof Aug 11 '14 at 14:27
  • 1
    I updated my answer with a SQL 2005 solution using XML and a link to some reasons to avoid the undocumented function `sys.fn_varbintohexstr`. – Paul Williams Aug 11 '14 at 15:07
0

CONVERT with style 2 to get a binary representation of the hexadecimal string;

... where TableA.bin_pk = CONVERT(VARBINARY, TableB.char_fk, 2)
Alex K.
  • 171,639
  • 30
  • 264
  • 288
  • CONVERT(VARBINARY, '0007914BFFEC4603A6900045492EFA1A', 2) gives 0x303030373931344246464543343630334136393030303435343932454641 – patil.rahulk Aug 11 '14 at 13:51
0

The correct aproach is to set both fields in the same datatype. in order to to do this create a new table say temp and use select into and convert:

select field1,...,convert(varchar(50),varbinary(16),fieldToConvert)...,fieldN

into myNewTable
apomene
  • 14,282
  • 9
  • 46
  • 72
0

Found the answer. I need to use 

master.dbo.fn_varbintohexstr (@source)

which converts a varbinary to varchar, and then works perfectly well for comparison in my scenario.

patil.rahulk
  • 574
  • 1
  • 3
  • 13