MATH000 MATH000 - 5 months ago 31
MySQL Question

Equivalent of MySQL HEX / UNHEX function in SQL Server?

Does SQL Server has an equivalent to the

HEX
and
UNHEX
MySQl functions?

Answer

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 :-)