T-SQL Hex String to VarBinary (Improved)

Peter DeBetta posted a while back with a function to take a hex string and convert it to varbinary  It has a couple of slight issues, the biggest of which is it can't handle an odd number of hex digits.  Below is my replacement, because it's using bigints the upper limit isn't as high, but it's good enough for most things:


CREATE FUNCTION dbo.HexStrToVarBinary(@hexstr varchar(8000))
RETURNS varbinary(8000)

DECLARE @hex char(1), @i int, @place bigint, @a bigint
SET @i = LEN(@hexstr)

set @place = convert(bigint,1)
SET @a = convert(bigint, 0)

WHILE (@i > 0 AND (substring(@hexstr, @i, 1) like '[0-9A-Fa-f]'))
SET @hex = SUBSTRING(@hexstr, @i, 1)
SET @a = @a +
convert(bigint, CASE WHEN @hex LIKE '[0-9]'
THEN CAST(@hex as int)
ELSE CAST(ASCII(UPPER(@hex))-55 as int) end * @place)
set @place = @place * convert(bigint,16)
SET @i = @i - 1


RETURN convert(varbinary(8000),@a)

Does xkcd Influence Microsoft Campus Design?

It appears that the new Microsoft Campus for the Entertainment and Devices Division is going to have a bar.  One wonders if they are not shooting for the "Ballmer Peak"...