### how to convert a binary to string with orginal format?

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

Regards

Eisen

10|10000 characters needed characters exceeded

Apr 30, 2018 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:

0000000000006861

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:

6,861

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;

Does that help?

--Kirby

10|10000 characters needed 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?

Thanks

• May 02, 2018 at 02:35 AM

Dear Kirby,

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

Regards

Eisen