How do you set a binary value from a string variable comprised of a binary literal in SQL Server?

user_9776216 0
Viewed 1187

I have a table with a column of binary literals converted to strings that I need to relate to a table containing the same value as binary(16)

Root table string Value '2F774578C33011E880D80050569C29CA'

table value I need to join to 0x2F774578C33011E880D80050569C29CA

is there a way to convert either the root table to Binary by simply adding the 0x to the string then declaring the string a literal value for the binary? or convert the binary to the string contained in the root.

I tried the following with no luck: 

DECLARE @jobIDBinary Binary(16) 
DECLARE @jobString Nvarchar(50) 

SET @jobIDBinary = '0x'+
(SELECT TOP (1) JobId

FROM  [Record])

error: Implicit conversion from data type nvarchar to binary is not allowed. Use the CONVERT function to run this query.

I also tried converting the other way: 

 DECLARE @convo varchar(max) 
 SET @convo = (SELECT TOP (1)
 [BinaryJobID]
 FROM [GAPClaims].[dbo].[Record2]

 WHERE binaryjobId IS NOT NULL )

Results = ,]óJ¾¶‡Á§\ê€

Thanks ahead of time.

tsql sql-server sql
2 Answers

You can convert your varbinary to varchar, and join on it (or relate it, as you stated).

declare @v varbinary(16) = 0x2F774578C33011E880D80050569C29CA
select @v, convert(varchar(256), @v,2)

declare @s varchar(256) = '2F774578C33011E880D80050569C29CA'
select @s, convert(varbinary(16),@s,2)

So, for you:

 DECLARE @convo varchar(max) 
 SET @convo = (SELECT TOP (1)
 convert(varchar(256),[BinaryJobID],2)
 FROM [GAPClaims].[dbo].[Record2]
 WHERE binaryjobId IS NOT NULL )

See the Binary section in the docs for why I used 2 in the convert statement.

user_61678550

Try this to convert string to binary:

CONVERT(BINARY(16), @jobString)

and Reverse:

CONVERT(VARCHAR(max), @jobBinary)
user_47949620
Related