Systems Engineering and RDBMS

Archive for June 11th, 2008

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

Posted in SQL Server | 3 Comments »

 
Follow

Get every new post delivered to your Inbox.

Join 77 other followers