Hex to String

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.

---

~Manu
http://sqlwithmanoj.wordpress.com

Answer 1

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.

http://social.msdn.microsoft.com/Forums/en-US/transactsql/thread/20e92e10-a0ab-4a53-a766-76f84bfd4e8c

 

---

AMB

Some guidelines for posting questions...

Answer 2

did you try master.sys.fn_varbintohexstr?

---

Craig

Answer 3

Thanks Hunchback,

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)

---

~Manu
http://sqlwithmanoj.wordpress.com

Answer 4

Thanks Craig,

But this is just opposite to what I was looking for.

---

~Manu
http://sqlwithmanoj.wordpress.com

Answer 5

This util also does the same https://codebeautify.org/hex-string-converter