Skip to Content
1

how to convert a binary to string with orginal format?

Apr 28 at 07:50 AM

68

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

Regards

Eisen

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
2

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

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?

Thanks

0
Eisen Wang
May 02 at 02:35 AM
1

Dear Kirby,

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

Regards

Eisen

Share
10 |10000 characters needed characters left characters exceeded