Varbinary To VarChar

• Raghavender Chavva

  SSCrazy Eights

  Points: 8669

  More actions

  March 2, 2010 at 4:05 am

  #156731

  Hi Team,

  Can we convert Varbinary data To VarChar ?

  Thanks In Advance!!

  Thank You.

  Regards,
  Raghavender Chavva

• Adi Cohn-120898

  SSC-Dedicated

  Points: 33944

  More actions

  March 2, 2010 at 4:38 am

  #1126957

  Yes you can. Here is an example:

  declare @vb varbinary(12)

  set @vb = 0x5375726520796F752063616E

  select convert(varchar(12),@vb)

  Adi

  --------------------------------------------------------------
  To know how to ask questions and increase the chances of getting asnwers:
  http://www.sqlservercentral.com/articles/Best+Practices/61537/

  For better answers on performance questions, click on the following...
  http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

• Hunterwood

  SSCrazy

  Points: 2124

  More actions

  March 2, 2010 at 4:56 am

  #1126967

  You can, but you need to take care about nul-values (i.e. ASCII NUL = char(0).), because these seem to act as string terminator:

  declare @bin varbinary(20)

  -- Converting to and from binary

  set @bin = cast('Te' as varbinary(10)) + cast('st' as varbinary(10))

  select @bin as [myBin], cast(@bin as varchar(20)) as [myChar]

  --Result:

  --myBinmyChar

  --0x54657374Test

  -- Test with a CHAR(0) in the middle of the binary string; the string is cut.

  set @bin = cast('Te' as varbinary(10)) + cast(char(0) as varbinary(1)) + cast('st' as varbinary(10))

  select @bin as [myBin], cast(@bin as varchar(20)) as [myChar]

  --Result:

  --myBinmyChar

  --0x5465007374Te

  /Markus