SQL Server convert varbinary to string

Question

I have a table that has a varbinary field like:

0x83838383838372723 .....

I would like to concatenate this varbinary field to a string, for example:

SELECT CONCAT('Varbinary value is', varbinary_field)
FROM MyTable

I expect the following string:

Varbinary value is 0x83838383838372723

I need to use concat so how to do it?

Er... This is tagged as SQL 2008 but CONCAT was introduced in 2012. Also, I believe you're looking for CONVERT(VARCHAR(MAX), myBinaryValue, 1) or something like that. See binary styles here — ZLK
– ZLK, Commented Dec 20, 2016 at 21:21

Community · Accepted Answer · 2017-04-13 12:42:36Z

2

You might use the built-in function fn_varbintohexstr:

DECLARE @SomeHexString VARBINARY(MAX)=CAST('This is just some text, which should be a HEX-string' AS VARBINARY(MAX));
SELECT @SomeHexString;

SELECT 'This is concatenated: ' + sys.fn_varbintohexstr(@SomeHexString)

This function existed in 2005 already, but was limited in length. Should be fine with your 2008 environment...

edited Apr 13, 2017 at 12:42

CommunityBot

11 silver badge

answered Dec 20, 2016 at 22:24

Gottfried Lesigang

67.6k9 gold badges60 silver badges124 bronze badges

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

Comments

Sean Lange · Accepted Answer · 2016-12-20 21:21:14Z

-1

Something like this should work. BTW, it seems the varbinary you posted is not really valid.

declare @Bin varbinary(max) = convert(varbinary(max), 'My binary value')

select concat('varbinary value is: ', cast(@Bin as varchar(max)))

answered Dec 20, 2016 at 21:21

Sean Lange

33.7k3 gold badges28 silver badges42 bronze badges

Collectives™ on Stack Overflow

SQL Server convert varbinary to string

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related