none
Conversion between data types RRS feed

  • Question

  • Lets say I execute: SELECT hashbytes('MD5','IMTIAZ')

    Which returns: 0x60D164C6B64EE81C7E7395C01D838FEE

    How do I get a varchar: 60D164C6B64EE81C7E7395C01D838FEE

    Not converted.

    How do I get the 0x removed from the string ?

     

    Regards

    Imtiaz

    Monday, January 16, 2006 10:29 PM

Answers

  • Ok...here's the answer...

     

    SELECT substring(upper(master.dbo.fn_varbintohexstr(hashbytes('MD5','SHELLEY'))),3,len(master.dbo.fn_varbintohexstr(hashbytes('MD5','IMTIAZ'))))

    Monday, January 16, 2006 11:48 PM
  • You have to write your own TSQL/SQLCLR scalar UDF to do the conversion from varbinary to hexadecimal string. Please do not use undocumented stored procedures like xp_varbintohexstr or fn_varbintohexstr. Undocumented objects can be dropped or modified in any release or even service pack of SQL Server. So you should not rely on such interfaces. It is easier to write your own code for these type of problems.
    Monday, January 16, 2006 11:54 PM

All replies

  • I have been googling to find a solution to this question.....

    I have acome across a few places to use the xp_varbintohexstr undocumented procedure. But in SQL 2005 what is the equivalent and any pointers in this direction will be of great help.

     

    Regards

    Imtiaz

    Monday, January 16, 2006 11:34 PM
  • Ok...here's the answer...

     

    SELECT substring(upper(master.dbo.fn_varbintohexstr(hashbytes('MD5','SHELLEY'))),3,len(master.dbo.fn_varbintohexstr(hashbytes('MD5','IMTIAZ'))))

    Monday, January 16, 2006 11:48 PM
  • You have to write your own TSQL/SQLCLR scalar UDF to do the conversion from varbinary to hexadecimal string. Please do not use undocumented stored procedures like xp_varbintohexstr or fn_varbintohexstr. Undocumented objects can be dropped or modified in any release or even service pack of SQL Server. So you should not rely on such interfaces. It is easier to write your own code for these type of problems.
    Monday, January 16, 2006 11:54 PM
  • That makes no sense.  It is already in the master db, why not expose this function as a global udf?  Clearly there is demand for it.
    Thursday, July 2, 2009 6:07 PM