Challenged by Hans Lindgren's stored procedures of the same name, I created this. Note that it produces strange results on non-hexadecimal strings, overflows at 0x80000000, and could have issues with byte-ordering on some architectures.

How does it work? Well, the distance between one after '9' (':') and 'A' is 7 in ASCII. Also, if I subtract 48 from an upper-cased hex-digit, digit & 16 will always be equal to 16. So I can mask that bit out, shift it down 4 bits (/16), multiply by 7, subtract from the original value, and come up with a value from 0 to 15. This can be done on all 8 digits in parallel, as you can see below.

I use CAST(CAST('1234ABCD' AS BINARY(8))AS BIGINT) to put the hex value 1234ABCD into a number I can manipulate, then subtract the value '00000000' (CAST(0x3030303030303030 AS BIGINT)), then mask out the hex overflow bits, shift right, multiply by 7, subtract to make the values 0x010203040A0B0C0D, then I shift the nybbles into the proper places and add.