SQL with Manoj

–> Question:

How can I convert the hex code ‘0x16004D616E75623232’ to string?

An online utility provides me its equivalent: http://www.string-functions.com/hex-string.aspx

But how can I do this by TSQL, CLR would be a second thought.
 

–> Answer:

Didn’t thought it was so simple, actually my hex string had ‘1600’ prefixed after 0x, on removing it I’m getting the expected o/p, which is:

SELECT CONVERT(varchar(64), 0x4D616E75623232, 0)

–> Answer from Hunchback:

If you are using SS 2008 or earlier, then check function CONVERT in BOL.

SELECT
  CONVERT(varbinary(64), '0x16004D616E75623232', 1),
  CONVERT(varchar(64), 0x16004D616E75623232, 1),
  CONVERT(varchar(64), 0x16004D616E75623232, 2);
GO

For lower versions you can use some tricks from Umachandar Jayachandran, link: http://social.msdn.microsoft.com/Forums/en-US/transactsql/thread/20e92e10-a0ab-4a53-a766-76f84bfd4e8c
 

Ref link