Archive for December 22, 2010

Convert Hex to String – MSDN TSQL forum

December 22, 2010 Leave a comment

–> Question:

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

An online utility provides me its equivalent:

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.

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

For lower versions you can use some tricks from Umachandar Jayachandran, link:

Ref link