Convert varchar to hexadecimal in sql server

Question

I want a function to convert a varchar to hexadecimal in sql server. Please help me.

P.S. I will use unhex() in Hive to try to get the original value back. This is because my data contains special characters and backslash and the HIVE external table does not recognise it

Answer 1

You can try to use CONVERT function:

SELECT CONVERT(VARBINARY(MAX), 'myText') 

Output:

0x6D7954657874

Answer 2

you can use

select try_convert(varbinary,varcharcolumn)