Conversion between data types

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

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

|||

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

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

|||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.