Systems Engineering and RDBMS

Converting a Hex String to a Varbinary or vice-versa

Posted by decipherinfosys on June 11, 2008

In SQL Server 2005 (haven’t tested in SQL Server 2008), if one wants to convert a varbinary to a hex string, one can simply do this:

DECLARE @test BINARY(16)
SET @test = NEWID()
SELECT master.dbo.fn_varbintohexstr(@test)

/*Output*/:

0xc23eed6b65c93e44a41a2818e274194f

One point of caution in this regard – the above mentioned function is an un-documented function.

If one wants to go from a hex string to a varbinary though, there is no such in-built function and one has to write a function in order to achieve this conversion or one can use dynamic SQL to achieve this. Here is how it can be done:

a) Usage of a CTE:
declare @binvalue varbinary(8000), @hexstring varchar(8000)
set @hexstring = ‘0x499104dc92dd27499da9ad8f56dcb437’
SET @binvalue = CAST(” AS VARBINARY(1));

IF SUBSTRING(@hexstring, 1, 2) = ‘0x’
set @hexstring = substring (@hexstring, 3, len(@hexstring));

WITH CTEDATA (hex, bin)
AS
(SELECT ‘0’, 0 UNION ALL
SELECT ‘1’, 1 UNION ALL
SELECT ‘2’, 2 UNION ALL
SELECT ‘3’, 3 UNION ALL
SELECT ‘4’, 4 UNION ALL
SELECT ‘5’, 5 UNION ALL
SELECT ‘6’, 6 UNION ALL
SELECT ‘7’, 7 UNION ALL
SELECT ‘8’, 8 UNION ALL
SELECT ‘9’, 9 UNION ALL
SELECT ‘A’, 10 UNION ALL
SELECT ‘B’, 11 UNION ALL
SELECT ‘C’, 12 UNION ALL
SELECT ‘D’, 13 UNION ALL
SELECT ‘E’, 14 UNION ALL
SELECT ‘F’, 15),
N1 (n) AS (SELECT 1 UNION ALL SELECT 1),
N2 (n) AS (SELECT 1 FROM N1 AS X, N1 AS Y),
N3 (n) AS (SELECT 1 FROM N2 AS X, N2 AS Y),
N4 (n) AS (SELECT 1 FROM N3 AS X, N3 AS Y),
N5 (n) AS (SELECT ROW_NUMBER() OVER(ORDER BY X.n) FROM N4 AS X, N4 AS Y)
select @binvalue = @binvalue + cast(sum(cast(REPLACE(SUBSTRING(@hexstring, n, 1), hex, case when n%2 = 0 then bin else bin * 16 end) as int)) as binary(1))
FROM N5 AS Nums
JOIN CTEDATA AS C
ON SUBSTRING(@hexstring, n, 1) = hex
WHERE n <= LEN(@hexstring)
group by case when n%2 = 0 then n-1 else n end
select @binvalue

/*Output*/

0x499104DC92DD27499DA9AD8F56DCB437

b) Usage of dynamic SQL using sp_executeSQL and parameters, you can do this:

declare @binvalue varbinary(8000), @hexstring varchar(8000), @sqlstring nvarchar(1000)
set @hexstring = ‘0x499104dc92dd27499da9ad8f56dcb437’
SET @sqlstring = ‘SELECT @binvalue = ‘ + @hexstring
EXEC sp_executesql @sqlstring, N’@binvalue VARBINARY(8000) OUTPUT’, @binvalue OUTPUT
SELECT @binvalue

/*Output*/

0x499104DC92DD27499DA9AD8F56DCB437

5 Responses to “Converting a Hex String to a Varbinary or vice-versa”

  1. […] by decipherinfosys on July 31, 2008 In one of our post, we had covered how to convert a hext string to a varbinary and vice-versa in SQL Server 2005. In […]

  2. Dave said

    Thanks for the post. Very useful!

  3. […] so simple: Converting a Hex String to a Varbinary or vice-versa Systems Engineering and RDBMS __________________ Master Gymnast (Self […]

  4. […] SQL 2005 , master.dbo.fn_varbintohexstr […]

  5. […] SQL 2005 , master.dbo.fn_varbintohexstr […]

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

 
%d bloggers like this: