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
Advertisements

5 responses to “SQL Server: Convert a string into a hex string and back #sql #sqlserver

  1. Pingback: Select convert | Greatmerchandi

  2. Fought with this all day, your post fixed my problem. Thanks!

  3. Yeah thanks, it’s good/efficient to be able to use BigInt (64 bits). It;’s normalized, the data warehouse can break out the details

  4. Great explanation, i search lots of site no body give right direction, the above example was amazing, Thanks

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s