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.

Same as this: https://stackoverflow.com/questions/12139073/sql-server-converting-varbinary-to-string — Prisoner, Jul 12 '17 at 08:01
@Prisoner But the 0x is trimmed from the output.I need the output include that — SujithTee, Jul 12 '17 at 08:08

score 10 · Accepted Answer · answered Jul 12 '17 at 08:12

10

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

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

answered Jul 12 '17 at 08:12

Kapil

987
5
11

score 3 · Answer 2 · answered Jul 12 '17 at 08:19

3

this also works for me :

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

just change your value with varbinary_val

answered Jul 12 '17 at 08:19

Fadi Nouh

324
2
12

Esteban P. · Answer 3 · 2017-07-12T08:17:12.920

0

should work like:

DECLARE @a BINARY(20) = 0x24000000008B0100000000
SELECT  CONVERT(varchar(max),@a,1), @a

edited Jul 12 '17 at 08:17

answered Jul 12 '17 at 08:03

Esteban P.

2,789
2
27
43

But the 0x is trimmed from the output.I need the output include that – SujithTee Jul 12 '17 at 08:07
@SujithTee i updated my post. I just changed the third parameter of convert from 2 to 1 – Esteban P. Jul 12 '17 at 08:17

Convert binary to varchar

3 Answers3