Skip to Content

how to convert a binary to string with orginal format?

Apr 28 at 07:50 AM


avatar image

Dear All,

Please kind help how to convert a binary object to a string with original format. eg.

create table #t1(a varbinary);

insert #t1 values(0x6861);

create table #t2(str varchar(100));


Then I found if I ran --

insert #t2 select convert(varchar(100),a) from #t1;

It's 'ha' inserted not '0x6861'

I also tried cast(a as varchar(100)).. but still 'ha' not '0x6861'...

Then even select ''||a from #t1 -- will result in 'ha'...

Please kind help me out. Thanks a lot



10 |10000 characters needed characters left characters exceeded
* Please Login or Register to Answer, Follow or Comment.

2 Answers

Best Answer
Kirby Gehman
Apr 30 at 04:15 PM

This is an interesting issue, because the "original format" of 0x6861 is not a string "0x6861". It's a numeric value in hex. Getting a string representation of that value is not as trivial as it seems. The only way that I found was to take advantage of the fact the the output of many of SAP IQs functions are strings, so the following actually works:

select inttohex(hextoint(a)) from #t1;

It returns this as a string:


We need to remove those leading zeroes. SAP IQ's LTRIM function doesn't take strings as parameters (it only trims leading spaces) so we have to try another way. Let's convert it to an INT:

select cast(inttohex(hextoint(a)) as int) from #t1;

This returns:


So let's convert it back to a string and add the hex leader "0x":

select '0x'||cast(inttohex(hextoint(a)) as int) from #t1;

This returns:

0x6861 (as a string, not a hex value)

I'm taking advantage of the automatic conversion of the integer to a string when the || operator is used, but you could do that explicitly if you wanted:

select '0x'||cast(cast(inttohex(hextoint(a)) as int) as char(10)) from #t1;

This returns the same answer.

Does that help?


Show 1 Share
10 |10000 characters needed characters left characters exceeded

But there's another quesion -- if the string is very long -- so that the "int" can't hold that string object, then how can we process it?


Eisen Wang
May 02 at 02:35 AM

Dear Kirby,

Thank you so much !!! Your conversion skills is so amazing!



10 |10000 characters needed characters left characters exceeded