SQL Server: Convert a string into a hex string and back #sql #sqlserver

Worth noting this one down as it’s not entirely obvious how to convert a string into a hex string or back in SQL Server, fortunately it is easy, there is a built in function called fn_varbintohexstr to go one way, great, but nothing I know of going the other, searching online I see people writing their own.

SELECT MASTER.dbo.fn_varbintohexstr(CAST('Help' AS VARBINARY)) -- '0x48656C70'

But it is even easier than that in both ways, the above function is not required:

-- VARCHAR TO VARBINARY
SELECT CONVERT(VARBINARY(MAX), 'Help') -- 0x48656C70

-- VARBINARY TO VARCHAR
SELECT CONVERT(VARCHAR(MAX), 0x48656c70) -- 'Help'

-- VARCHAR TO (HEX) VARCHAR
SELECT CONVERT(VARCHAR(MAX), CONVERT(VARBINARY(MAX), 'Help'), 1) -- '0x48656C70'

-- (HEX) VARCHAR TO VARCHAR
SELECT CONVERT(VARCHAR(MAX), CONVERT(VARBINARY(MAX), '0x48656c70', 1)) -- Requires 0x, returns 'Help'
SELECT CONVERT(VARCHAR(MAX), CONVERT(VARBINARY(MAX), '48656c70', 2)) -- Assumes 0x string, no 0x wanted, returns 'Help'

-- One to one pitfall

SELECT CONVERT(VARCHAR(MAX), CONVERT(VARBINARY(MAX), '0x48656c70')) -- Same as adding 0 as last parameter, this is 1:1 conversion, so you get '0x48656c70' back