0

I'm just using some simple sql to try and get it working and then I will implement it into my program.

So my code is below:

select convert(varbinary(16), cast('63' as int)))

My result from this query is 0x000003F which is not what I wanted, I was expecting 000111111.

Does anyone know why it does this and how I can get it to display the number as 1's and 0's, it would be greatly appreciated, Thanks. This is using MSSql.

Vadim Kotov
  • 8,084
  • 8
  • 48
  • 62
Jack Tyler
  • 509
  • 6
  • 18
  • For Postgres, use: `63::bit(16)` –  Mar 13 '17 at 11:59
  • `select conv ( 63, 10, 2);` in MySql ( [tested on 5.7.12](http://rextester.com/EYYE27767) ) – collapsar Mar 13 '17 at 12:11
  • `varbinary` is not a binary integer. Whether the printout is decimal, binary or hexa-decimal (for example) is just a representation of the underlying number, not a separate data type. `varbinary` is just a variable-length array of bytes, analogical to `varchar` being a variable-length array of characters. The `0x000003F` you're seeing is just what Management studio shows you when you look at a `varbinary` field - a hexa-decimal representation of the bytes of data. You've just converted the integer into four bytes of data. – Luaan Mar 13 '17 at 14:02

2 Answers2

0

As collapsar notes in MySQL, you need to specify base 2, which is what you are missing here. Without that, MySQL defaults to hexadecimal encoding here but you need to provide a number (63), a starting base (10), and a final base (2).

Putting this together you get select conv(63, 10, 2) sqlfiddle

Chris Travers
  • 25,424
  • 6
  • 65
  • 182
0

In MSSQL I think you could try this:

DECLARE @var1 AS int;
SET @var1=12;
WITH A AS (
    SELECT 0 AS ORD, @var1 AS NUMBER, CAST('' AS VARCHAR(20)) AS BITS
    UNION ALL 
    SELECT ORD+1, NUMBER/2,  CAST(BITS+CAST(NUMBER%2 AS VARCHAR(20)) AS VARCHAR(20))
          FROM A 
          WHERE  NUMBER>0)
SELECT RIGHT('000000000000000'+ CASE WHEN BITS='' THEN '0' ELSE REVERSE(BITS) END,16) AS BIN_VALUE
FROM A
WHERE NUMBER=0;

Ouput:

BIN_VALUE

0000000000001100

etsa
  • 5,020
  • 1
  • 7
  • 18
  • Wow this works brilliantly, thank you. Is there a way to also show the preceding 0's so that everything return is the same length for example 8 bits or 16 bits – Jack Tyler Mar 13 '17 at 13:54
  • I'm really impressed at your MsSql skills and to be able to do that in such a short amount of time, Thank you. – Jack Tyler Mar 13 '17 at 14:02