A simpler way to convert a hexadecimal string to varbinary

This isn't perf-related like most of my earlier posts, but I thought it was useful enough that I should share it.  We recently had a situation where we had to convert a hexadecimal string representation of a binary value to a true binary (e.g. varchar value '0x1234abcdef' --> varbinary 0x1234ABCDEF).  There's a built-in function in SQL (fn_varbintohexstr) to convert from varbinary to a hex-formatted varchar value, but nothing to convert in the opposite direction.  A smart guy on my team (Ahmed Ayad) came up with a clever solution.  There are other solutions floating around out there that do this same conversion, but I don't think I've ever run across one as nice and tidy as this.  It takes advantage of the fact that SQL's parser already knows how to convert a hex string representiation of a binary into a "real" binary. 

IF

OBJECT_ID ('usp_hexstrtovarbin', 'P') IS NOT NULL DROP PROC usp_hexstrtovarbin
GO
CREATE PROC usp_hexstrtovarbin @hexstr varchar(3990), @bin varbinary (4000) OUTPUT AS
DECLARE @sql nvarchar(4000)
SET @sql = 'SET @bin=' + @hexstr
EXEC sp_executesql @sql, N'@bin varbinary(4000) OUTPUT', @bin OUTPUT
GO 

Usage is straightforward: just call the proc, passing it the hex-formatted string and an output param to receive the converted value. For example:

DECLARE @hexstr varchar(max), @bin varbinary (4000)
SET @hexstr = '0x1234abcdef'

EXEC usp_hexstrtovarbin @hexstr, @bin OUTPUT

SELECT @bin
GO

Unfortunately, SQL won't allow you to use sp_executesql within a user-defined function, so a disadvantage of this approach is that you can't move this into a scalar function for use in queries like "SELECT dbo.fn_hexstrtovarbin(mycolumn) FROM mytable". 

 

fingerprints_screenshot_1.png

Comments

• Anonymous
  July 30, 2007
  Cool!.To round out here are some data validation strings on the ipnut @hexstr variable.IF LEN(@hexstr)%2 <> 0 RAISERROR/RETURNIF LEFT(@hexstr,2) <> '0x' RAISRROR/RETURNdeclare @i int; select @i = 3;while @i < len(@hexstr) + 1begin

  if upper(substring(@hexstr,@i,1)) not  in ('1','2','3','4','5','6','7','8','9','0','A','B','C','D','E','F')    RAISERROR/RETURNselect @i = @i + 1;-- select @i += 1; -- SQL 2008!

  end;
• Anonymous
  April 21, 2009
  Or you could use the built in function:select sys.fn_cdc_hexstrtobin('DD0B84B393E507E95DF020794DDAB44C');