Convert binary to varchar

Question

I need to change the binary output to varchar(string), but it must same as to be the binary output. But while converting the binary(hex) value to varchar, empty set will be returned. Kindly help me.

E.x

If this is my binary value 0x24000000008B0100000000.

I need the same 0x24000000008B0100000000 output after converting it into string.

Solution 1

declare @val binary(20)
set @val=0x24000000008B0100000000

select @val, CONVERT(varchar(max),@val,1)

Solution 2

this also works for me :

SELECT CONVERT(VARCHAR(1000), varbinary_val, 1);

just change your value with varbinary_val

Comments

[Prisoner]

Same as this: stackoverflow.com/questions/12139073/…

[SujithTee]

@Prisoner But the 0x is trimmed from the output.I need the output include that

[SujithTee]

But the 0x is trimmed from the output.I need the output include that

[Esteban P.]

@SujithTee i updated my post. I just changed the third parameter of convert from 2 to 1