String to binary

• dfine

  Ten Centuries

  Points: 1251

  More actions

  August 9, 2011 at 5:03 am

  #245940

  Hello,

  I like to convert string to binary in SQL Server 2000, the code I used to do this is

  declare @varchar as varchar(10)

  set @varchar='Hello'

  select convert(binary(8),@varchar)

  but the output received is in HEXA (0x48656C6C6F000000). but I need the output should be in binary (0100 1000 0110 0101 0110 110 0011 0110 0011 01111) is there any system function or procedure exists?

  Thanks

  Raj

  [font="Calibri"]Raj[/font]

• Lowell

  SSC Guru

  Points: 323518

  More actions

  August 9, 2011 at 5:36 am

  #1365782

  you mean a binary representation/bitmap, i guess?

  i've got this saved in my snippets, but it's for 2005 and above because it's using a CTE.

  you might be able to adapt it to use subqueries instead:

  /*--results

  1101111000000000000000000000000

  */

  DECLARE @IntVal int;

  SET @IntVal = 0x48656C6C6F000000;

  WITH CTE (IntVal, BinVal, FinalBin) AS

  (SELECT @IntVal IntVal, @IntVal % 2 BinVal, CONVERT(varchar(MAX),@IntVal % 2) FinalBin

  UNION ALL

  SELECT IntVal / 2, (IntVal / 2) % 2, CONVERT(varchar(MAX),(IntVal / 2) % 2) + FinalBin FinalBin

  FROM CTE

  WHERE IntVal / 2 > 0)

  SELECT right('0000000000000000000000000000000' + FinalBin ,31)

  FROM CTE

  WHERE IntVal =

  (SELECT MIN(IntVal)

  FROM CTE);

  Lowell

  ---

  --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!