How to convert a varbinary value as varchar

• billz

  SSC Journeyman

  Points: 94

  More actions

  September 22, 2003 at 1:37 pm

  #81052

  Hi,

  I need to convert the display representation of a varbinary value as varchar. e.g., the result of

  SELECT CAST(0x41 AS varbinary)

  can be converted and assigned to a varchar variable with value of '0x41' instead of 'A'.

  Could anyone help me how to do that?

  Thanks

  Bill

• Jonathan

  SSC-Insane

  Points: 20427

  More actions

  September 22, 2003 at 6:21 pm

  #475336

  This is in BOL (acdata.chm::/ac_8_qd_14_8k6m.htm):

  CREATE PROCEDURE sp_hexadecimal

  @binvalue varbinary(255),

  @hexvalue varchar(255) OUTPUT

  AS

  DECLARE @charvalue varchar(255)

  DECLARE @i int

  DECLARE @length int

  DECLARE @hexstring char(16)

  SELECT @charvalue = '0x'

  SELECT @i = 1

  SELECT @length = DATALENGTH(@binvalue)

  SELECT @hexstring = '0123456789abcdef'

  WHILE (@i <= @length)

  BEGIN

  DECLARE @tempint int

  DECLARE @firstint int

  DECLARE @secondint int

  SELECT @tempint = CONVERT(int, SUBSTRING(@binvalue,@i,1))

  SELECT @firstint = FLOOR(@tempint/16)

  SELECT @secondint = @tempint - (@firstint*16)

  SELECT @charvalue = @charvalue +

  SUBSTRING(@hexstring, @firstint+1, 1) +

  SUBSTRING(@hexstring, @secondint+1, 1)

  SELECT @i = @i + 1

  END

  SELECT @hexvalue = @charvalue

  --Jonathan

  
  
  --Jonathan

• billz

  SSC Journeyman

  Points: 94

  More actions

  September 23, 2003 at 8:23 am

  #475425

  Thanks, Jonathan

  That's great.

  Bill