Convert string representation of VARBINARY to VARBINARY value

Question

I need to take a string representation of a VARBINARY value and convert it back to the actual VARBINARY value, but how?

I want to take the results of the following query and convert it back to the VARBINARY value so I can decrypt it to it's original value?

CREATE TABLE Table1 (
    FirstName varchar(32) NOT NULL,
    LastName varchar(32) NOT NULL,
    Social varbinary(128) NOT NULL)

OPEN SYMMETRIC KEY KEY_NAME
DECRYPTION BY CERTIFICATE CERT

INSERT INTO Table1(FirstName,LastName,Social)
VALUES ('John','Doe',ENCRYPTBYKEY(Key_Guid('KEY_NAME'),'123-45-6789'))

SELECT CONVERT(varchar(max), social,1) Social
FROM Table1
WHERE LastName='Doe'

S3S · Accepted Answer · 2019-01-16 19:39:28Z

4

So, generally speaking you will use convert to go to and from varbinary and varchar. You'll want to use 2 for the style instead of 1 to get the string representation. If you want the literal conversion, use the default of 0.

DB FIDDLE

declare @v varbinary(128) = (select cast('ThisIsMyPassword' as varbinary(128)))

select
    @v as Val
    ,convert(varchar(max),@v,2) as String
    ,convert(varchar(max),@v,0) as Literal

See the docs on when to use the styles here

edited Jan 16, 2019 at 19:39

answered Jan 16, 2019 at 19:34

S3S

25.2k5 gold badges29 silver badges48 bronze badges

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

2 Comments

BashfulCoder Over a year ago

That was exactly what I needed. Thank you. I have always been a bit cloudy in my thinking when it comes to binary types and conversions.

S3S Over a year ago

No worries at all @user3130643. It can get messy and tricky for sure!

Collectives™ on Stack Overflow

Convert string representation of VARBINARY to VARBINARY value

1 Answer 1

2 Comments

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Related