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