This function converts a hexadecimal string (i.e. 0x010A020B) to it's varbinary equivalent value. This is a reasonably fast implementation. For an alternative, but slower, XQuery based alternative, see Converting from hex string to varbinary and vice versa on the SQL Server Engine Tips blog.
The function returns null for an invalid input, i.e. any non-hex characters found. The function will left pad the input with a leading zero if an odd number of hex characters are supplied, either upper or lower case hex characters are permitted, and finally, a leading hex designator (0x) is optional.
SQL Server includes an undocumented system function sys.fn_varbintohexstr (dbo.fn_varbintohexstr in SQL Server 2000) to perform the reverse conversion, but no equivalent to the function here. SQL Server 2008 now supports this conversion natively in the CONVERT function.
This function is suitable for SQL Server 2005 and 2008 as well as SQL Server 2000 (just remove the RETURNS NULL ON NULL INPUT function option).