SQL Clone
SQLServerCentral is supported by Redgate
Log in  ::  Register  ::  Not logged in

Get your favorite SSC scripts directly in SSMS with the free SQL Scripts addin. Search for scripts directly from SSMS, and instantly access any saved scripts in your SSC briefcase from the favorites tab.
Download now (direct download link)

faster dbo.ufn_vbintohexstr - varbinary to hex

By Aaron West,

Here's an alternative to Clinton Herring's ufn_vbintohexstr which should be much faster with large varbinary values. First, in his original version, the inner-loop CASE statements can be replaced with this: select @value = @value + CHAR(@vbin/16+48+(@vbin+96)/256*7) +CHAR(@vbin&15+48+((@vbin&15)+6)/16*7) How does it work? By adding 6 to a hex-digit in (@vbin&15), you have a value from 16 to 21. If you integer-divide by 16, it's always 1. multiply by 7, and you have the amount needed to add to turn 0x0A to 'A', given that 0x30 (48) has already been added. (Okay, I know it's confusing) Anyway, the neat thing about this technique is that it can be done for 4 bytes in parallel. Below is the entire arith-8 version, producing 8-hex-digits at a time.

Total article views: 78 | Views in the last 30 days: 1