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.
Usage is straightforward: just call the proc, passing it the hex-formatted string and an output param to receive the converted value. For example:
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 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!
Or you could use the built in function: