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
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

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


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”. 



Comments (2)

  1. Anonymous says:


    To round out here are some data validation strings on the ipnut @hexstr variable.

    IF LEN(@hexstr)%2 <> 0 RAISERROR/RETURN

    IF LEFT(@hexstr,2) <> ‘0x’ RAISRROR/RETURN

    declare @i int; select @i = 3;

    while @i < len(@hexstr) + 1


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


  2. Anonymous says:

    Or you could use the built in function:

    select sys.fn_cdc_hexstrtobin(‘DD0B84B393E507E95DF020794DDAB44C’);