Post reply

concatenation binary to char

  • February 21, 2007 at 2:58 am

    #172143

    Hi,

    In one column I've a binary data = 0x098F6BCD4621D373CADE4E832627B4F6

    I'll put in an other column ( varChar() ) the followed result :

    MD5:89C83C39E77B3C9168071882D2034DDE

    I've tried this:

    DECLARE

    @mybin1 binary(16)

    SET

    @mybin1 = 0x098F6BCD4621D373CADE4E832627B4F6

    SELECT

    @mybin1

    SELECT

    'MD5:' + right( @mybin1, len(@mybin1)-2 )

     

    and here is the result:

    ----------------------------------

    0x098F6BCD4621D373CADE4E832627B4F6

    (1 row(s) affected)

     

    --------------------

    MD5:kÍF!ÓsÊÞNƒ&'´ö

    (1 row(s) affected)

     

     

    Definitely not what I expected.

    Explanation and suggestion is welcome.

    Thanks

     

     

  • February 21, 2007 at 8:24 am

    #690926

    As far as suggestions go, You can use

    select master.dbo.fn_varbintohexstr(@mybin1)

    Be aware that fn_varbintohexstr is and undocumented function, and thus

    unsupported and may not be included in future versions of SQL Server.

    As far as why it didn't work the way you had implemented it: Basically, you can't treat binary data in the same way you treat string data. All data is stored in memory as binary(and displayed in hexadecimal). With a binary datatype, you are not storing the data as a string as that would be inefficient; If 0x61736466 were a string, it would actually be stored as 0x30783631373336343636 in memory (taking up more that double the number of bytes).

    When you convert binary to a varchar, you are telling sql server to convert each byte into a character. Therefore, 0x61736466 becomes 'asdf' because HEX(61) = DEC(97) which corresponds to the ANSI value for an lowercase 'a' (hint: execute PRINT CHAR(97) ).

    It's pretty early for me, and I'm not sure how clear I've made this. Please feel free to ask any questions you have.

    SQL guy and Houston Magician

  • February 21, 2007 at 9:09 am

    #690951

    Actually, what I think you want is:

    DECLARE @mybin1 binary(16)

SET @mybin1 = 0x098F6BCD4621D373CADE4E832627B4F6

SELECT @mybin1
select sys.fn_varbintohexsubstring(0, @mybin1,1,0)

    SQL guy and Houston Magician

  • February 22, 2007 at 2:53 am

    #691090

    Thanks a lot, that greater help me. I also found the code of fn_varbintohexstr, so I can include it in my own DB.

    I will look deeply in your explanation later, but I see the way it's working.

    I really appreciate.

     

    Ruddy

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply