how to convert a binary to string with orginal format?

[former_member232292]

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

[former_member231106 · Accepted solution]

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;

This returns the same answer.

Does that help?

--Kirby

[former_member232292 · Accepted solution]

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

[former_member232292]

Dear Kirby,

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

Regards

Eisen