HexStr to VarBinary in poor TSQL

T-SQL: Convert Hex String to VarBinary...

To convert between int and hex values, by referring to this post:
SELECT ‘Int -> Hex’
SELECT CONVERT(VARBINARY(8), 16777215)
SELECT ‘Hex -> Int’
SELECT CONVERT(INT, 0xFFFFFF)


To convert a VarBinary to a HexString, by referring to Peter DeBetta’s post, there is a function in SQL Server 2005 with the name "sys.fn_varbintohexstr" to do the job.
To convert a HexString to a VarBinary, there is no built-in function to do so. Peter DeBetta wrote a function to do this in above post link, and later at Michael.Net’s blog post, there is an improved function to do the job. I’ll also document here for my reference.

CREATE FUNCTION dbo.HexStrToVarBinary(@hexstr varchar(8000))
RETURNS varbinary(8000)
AS
BEGIN
    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]'))
     BEGIN
        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
   
     END

    RETURN convert(varbinary(8000),@a)
END
GO