Convert varchar to binary or vice versa

• tripri

  SSCommitted

  Points: 1584

  More actions

  October 27, 2011 at 10:58 am

  #262100

  Hi,

  How can i convert a binary to varchar datatype or a varchar to binary data type.

• October 28, 2011 at 6:36 am

  This was removed by the editor as SPAM

• GSquared

  SSC Guru

  Points: 260824

  More actions

  October 28, 2011 at 7:26 am

  #1400620

  Do you want a binary conversion, or an explicit string conversion.

  E.g.:

  SELECT CAST('Hello world' AS VARBINARY(100));

  Gives: 0x48656C6C6F20776F726C64

  That's a binary conversion. Reverse it:

  SELECT CAST(0x48656C6C6F20776F726C64 AS VARCHAR(100));

  And you get: Hello world

  A literal conversion would be:

  DECLARE @Bin VARBINARY(100) = 0x01000000a60b0000,

  @STR CHAR(16) = '0123456789abcdef' ;

  SELECT '0x'

  + (SELECT SUBSTRING('0123456789abcdef',

  CAST(SUBSTRING(@Bin, Number, 1) AS INT) / 16 + 1,

  1) + SUBSTRING('0123456789abcdef',

  CAST(SUBSTRING(@Bin, Number, 1) AS INT)

  % 16 + 1, 1)

  FROM dbo.Numbers

  WHERE Number <= DATALENGTH(@Bin)

  AND Number > 0

  FOR

  XML PATH(''));

  That takes the binary number 0x01000000a60b0000 and produces a string literal of "0x01000000a60b0000" from it. CAST/CONVERT won't do that.

  This method requires a Numbers or Tally table, to parse out the binary into components.

  - Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
  Property of The Thread

  "Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon