Equivalent of MySQL HEX / UNHEX function in SQL Server?

Question

Does SQL Server has an equivalent to the HEX and UNHEX MySQl functions?

Answer 1

Update 2022: This is outdated, read about CONVERT() together with binary types.

What are you going to do?

Something like a script generation?

There might be better approaches for your issue, but you do not provide many details...

Not quite as slim but this would work

--This will show up like needed, but it will not be a string
SELECT CAST('abc' AS VARBINARY(MAX))

--this is the string equivalent
SELECT sys.fn_varbintohexstr(CAST('abc' AS VARBINARY(MAX)));

--This will turn the string equivalent back to varbinary
SELECT sys.fn_cdc_hexstrtobin('0x616263')

--And this will return the original string
SELECT CAST(sys.fn_cdc_hexstrtobin('0x616263') AS VARCHAR(MAX));

###One hint

If you deal with UNICODE you can check the changes if you set N'abc' instead of 'abc' and in den final line you'd have to convert '0x610062006300' to NVARCHAR.

###Another hint

If you need this more often you might put this into an UDF, than it is as eays as with MySQL :-)