Hans asked if it could be faster. This is about 10% faster; not much. His is admittedly more readable, and mine will act very strangely with invalid hex digits.

How does it work? I'm converting the string '1234' to the value 0x31323334 (for example), then subtracting '0000' so that it is 0-based in each byte (CONVERT(INT,0x30303030) = 808464432), then masking out each byte, subtracting 7 if the value is between 16 and 31 instead of 0-9 (since the hexadecimal digits will always have that bit set), then shifting to the appropriate place with division, and adding the nybbles together.

Confused? Me too. But it works. (So use Hans's; it's easier to understand. Not sure a 10% to 20% speed boost is worth this.)