ufn_vbintohexstr

,

A function to convert varbinary to hex string. Have you every tried to deal with varbinary via T-SQL? Passwords and SIDs are of this datatype. In Query Analyzer this datatype displays as hex string but under the hood it is binary. Try concatenating a varbinary to a string in a print statement and you get a datatype mismatch error. Try to convert this datatype to varchar and you get garbage. You can use the little know extended stored procedure xp_varbintohexstr. It takes one varbinary input parameter and outputs the equivalent hex string as an OUTPUT parameter but it is not well suited for set processing. Ufn_vbintohexstr is. You can use it directly in a select statement against a column if necessary. Remember to include the parentheses when using this function in a select statement but don't include them when calling it via Exec. The input is a varbinary(256) so any length variable up to this max can be converted. The function returns the equivalent hex string of the varbinary input value. BTW a hex string doesn't convert back into a varbinary via the convert function so take care using the results and don't forget to include 2 extra position for the leading '0x' in your output variables. -- Fast & efficient update to original.

Alter function dbo.ufn_vbintohexstr (
      @vbin_in varbinary(256)
      )
returns varchar(514)
as

/***5***10***15***20***25***30***35***40***45***50***55***60***65***70***75**/
--Name        : dbo.ufn_vbintohexstr        for SQL 2K
--
--Description : Converts a binary to the equivalent hex string, i.e., 
--              11111111 converts to 0xFF
--
--Parameters  : @vbin_in - the varbinary value to convert; returns the hex
--                         string equivalent.
--
--Comments    : A binary byte such as 11111111 requires 2 char characters and
--              the leading '0x' in order to display correctly the hex string
--              so a binary(16) would require a char(34) for correct display
--              or storage.
--
--Note        : This method shifts nybbles to get one nybble per individual
--              byte and then converts the values to ascii equivalents by 
--              first adding 48 (0x30) to each elongated byte and then uses
--              a step function to add the necessary 7 if A-F. A final convert
--              to varchar returns the hex string value. Fast & efficient.
--
--Method from : Aaron West
--Author      : Clinton Herring
--Date        : 05/12/2005
--
--History     : 
--
/***5***10***15***20***25***30***35***40***45***50***55***60***65***70***75**/

Begin
   declare @big bigint
   declare @len int
   declare @loop int
   declare @value varbinary(514)
   set @value = 0x
   set @loop = 1
   set @len = Datalength(@vbin_in) 
   while @loop <= @len
      begin
         set @big = CAST(CAST(Substring(@vbin_in,@loop,4)as binary(4)) as bigint)
         set @big = 
            (@big&0xF0000000)*0x10000000
           |(@big&0x0F000000)*0x01000000
           |(@big&0x00F00000)*0x00100000
           |(@big&0x000F0000)*0x00010000
           |(@big&0x0000F000)*0x00001000
           |(@big&0x00000F00)*0x00000100
           |(@big&0x000000F0)*0x00000010
           |(@big&0x0000000F) 
         select @value = @value + CAST(@big | 0x3030303030303030 + ((@big+0x0606060606060606)/16&0x0101010101010101)*7 as binary(8))
         set @loop = @loop + 4
      end
   return '0x'+ CAST(LEFT(@value, @len*2) as varchar(514))
end

Rate

5 (2)

Share

Share

Rate

5 (2)