Converting varbinary to char ?

[JFoushee]

Is it possible to convert a varbinary(85) to a string?

If I select the varbinary field, I get a nice representation, as in '0x010500000000000515000000894D12106540827C6E34DF12370C0000' .

But it seems that once I try to convert to a integer, it doesn't convert correctly (because almost-similar patterns yield the same number), and converting to a char gives back special characters.

Is there a way to convert to a varchar with the similar layout as above?

I'd use the Forum Search, but it seems to be down.

Thanks!

 

[rt63]

The T-SQL help says that conversions from binary/varbinary to int/char/varchar may give errors/truncate data or give erratic results.

When you try to convert to type int, it picks up the last 8 digits in the varbinary data and converts it to integer. So in your case it is converting 0x370C0000 to int
= 55*256*256*256 (0x37) + 12*256*256 (0x0C) = 923533312
So any pattern having these 8 digits as last 8 digits will always convert to the same int value.

If you feel that this varbinary data actually contains some integer value, you can try retrieving it in parts using substring function. In your varbinary data, there are actually 28 bytes - so u can retrieve data in 7 attempts (each time it will convert only 4 bytes as integer)

e.g.
convert(int, substring(<varbinary field>,1,4))
convert(int, substring(<varbinary field>,5,4))
and so on..



RT

 

[JFoushee]

Your response got me to thinking, and I did a

Convert(uniqueidentifier,varbinary_field)

and it seems to work fine. Unless you see otherwise?

 

[rt63]

Depends what you expect the data in your field. Let me explain how the conversion from varbinary to uniqueindetifier will work. Let us say your varbinary is

0x0102030405060708090A0B0C0D0E0F10

It will return the output as

04030201-0605-0807-090A-0B0C0D0E0F10

Basically, it converts the left 32 digits in the varbinary data. In case the varbinary data has odd digits, it will append a 0 on the left and do its conversion based on left 32 digits which is as follows (calling 2 digits as 1 set, there will be 16 sets)

set4, set3, set2, set1 , - , set6, set5, -, set8, set7, -,set9,set10,-,set11,set12,set13,set14,set15,set16

Any digits beyond 32 will be ignored and any varbinary having same 32 starting digits will return the same value of uniqueidentifier. The value will differ for varbinary having odd/even digits as a 0 will be appended when odd digits.

So you have to judge whether this is Ok with you or not.




RT