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

Question

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.

why not just use convert or cast?

Doug Coats
– Doug Coats

2019-01-14 17:15:52 +00:00
Commented Jan 14, 2019 at 17:15 — Doug Coats
– Doug Coats, Commented Jan 14, 2019 at 17:15

S3S · Accepted Answer · 2019-01-14 17:35:25Z

1

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.

edited Jan 14, 2019 at 17:35

answered Jan 14, 2019 at 17:20

S3S

25.2k5 gold badges29 silver badges48 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Siavash Rostami · Accepted Answer · 2019-01-14 17:19:20Z

0

Try this to convert string to binary:

CONVERT(BINARY(16), @jobString)

and Reverse:

CONVERT(VARCHAR(max), @jobBinary)

answered Jan 14, 2019 at 17:19

Siavash Rostami

1,9334 gold badges18 silver badges33 bronze badges

3 Comments

user9776216 Over a year ago

Thank you, but converting to varchar(max) still translated the data in the binary to character rather than the string of text contained in the binary column. results = ,]óJ¾¶‡Á§\ê€

S3S Over a year ago

You need to specify the style, since 0 is the default.

Siavash Rostami Over a year ago

@user9776216 I just didn't catch the part that you want to prepend the binary prefix. So this was only general conversion examples. Having read your question again, i see your point :)

Collectives™ on Stack Overflow

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

2 Answers 2

Comments

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related