Converting a Hex String to a Varbinary or vice versa

Converting a Hex String to a Varbinary or vice versa

• Lrose

  Please take a look at the links below for some options:

  Integer value to hex

  http://www.umachandar.com/technical/SQL6x70Scripts/Main89.htm

  Hex to integer

  http://www.umachandar.com/technical/SQL70Scripts/Main29.htm

  If you don't have a table of numbers (which the 2nd link uses), you can use code like below:

  declare @t varchar (8)
  select @t = 'FFFFFFFF'
  select sum( (charindex( lower( substring( hexstr , number + 1, 1 ) ), hexchars ) - 1) * convert( decimal( 28 , 0 ) , power( 16 , number ) )
  )
  from
  (
  select reverse( @t ) as hexstr, '0123456789abcdef' as hexchars
  ) as h1
  cross join
  (select 0 union all select 1 union all select 2 union all select 3 union all
  select 4 union all select 5 union all select 6 union all select 7) as n(number)
  go

  You can extend this to generate 64-bit integer/bigint values also.

  
  
  

• NEC MultiSync90GX2

  For the opposite, the easiest way is to use the built-in (but undocumented) function, fn_varbintohexstr:

   

  DECLARE @x BINARY(16)

  SET @x = NEWID()

  SELECT master.dbo.fn_varbintohexstr(@x)

   

  ... Going the other way around is not quite so straightforward.  I usually use dynamic SQL:

   

  DECLARE @y CHAR(34)

  SET @y = '0x499104dc92dd27499da9ad8f56dcb437'

  DECLARE @x BINARY(16)

  DECLARE @sql NVARCHAR(200)

  SET @sql = 'SELECT @x = ' + @y

  EXEC sp_executesql

  @sql,

  N'@x BINARY(16) OUTPUT',

  @x OUTPUT

  SELECT @x

  
  --
  Adam Machanic
  Pro SQL Server 2005, available now
  http://www..apress.com/book/bookDisplay.html bID=457
  --

   

   

  <Orphaned Zombie@discussions.microsoft.com> wrote in message news:76360e15-52e8-499f-87b5-44f9eb1f0579@discussions.microsoft.com...

  I'm using SQL Server 2005. How can I convert a hex string to a varbinary or the opposite of that

  
  
• Converting a Hex String to a Varbinary or vice versa