Convert integer into binary format in SQL Server

Question

Convert integer into binary format in SQL Server

I am working on an online SQL exercise and trying to convert a series of integer number into binary numbers without leading 0. The database engine used is MS SQL Server.

The following is the original tables:

And the correct output should be this:

trip_no   trip_no_bi
1100     10001001100
1101     10001001101
1123     10001100011
1124     10001100100
1145     10001111001
1146     10001111010
1181     10010011101
1182     10010011110
1187     10010100011
1188     10010100100
1195     10010101011
1196     10010101100
7771     1111001011011
7772     1111001011100
7773     1111001011101
7774     1111001011110
7775     1111001011111
7776     1111001100000
7777     1111001100001
7778     1111001100010
8881     10001010110001
8882     10001010110010

I tried to use cast((trip_no) as varbinary) syntax but the result is weird. For example, when I used cast() function to convert 1100 on the exercise webpage it returned L, and the remaining output was this:

while when I tried this in MS SQL Server Management Studio it returned:

trip_no  trip_no_bi
1100     0x0000044C
1101     0x0000044D
1123     0x00000463
1124     0x00000464
1145     0x00000479
1146     0x0000047A
1181     0x0000049D
1182     0x0000049E
1187     0x000004A3
1188     0x000004A4
1195     0x000004AB
1196     0x000004AC
7771     0x00001E5B
7772     0x00001E5C
7773     0x00001E5D
7774     0x00001E5E
7775     0x00001E5F
7776     0x00001E60
7777     0x00001E61
7778     0x00001E62
8881     0x000022B1
8882     0x000022B2

I cannot figure out how this happened. Please help.

In contrast to the situation asked in this link: SQL Server Convert integer to binary string

I am not asking for a reusable solution cuz the exercise only allows an one-step solution...

Since the question mentioned that the desired output is its binary number representation (without leading zeroes), I guess it is either INT or VARCHAR.

Also please ignore the leading 0 thing as of right now since it is easy to get rid of it.

I should have been more clear on this: after seeing the first answer I realized this question is essentially asking for a series of conversion between numbers and strings, with formula to obtain binary number inside the query. I don't think it is asking for a binary conversion function, yet it is asking for "Calculate the binary number of the original trip_no, present it, and trim down the leading 0".

sql

sql-server

binary

type-conversion

sqldatatypes

asked on Stack Overflow Apr 7, 2016 by

PURWU • edited May 23, 2017 by

Community

2 Answers

A bit late, but here's another approach using bitwise AND. The value of each bit is calculated as a string, then the values of all the bits concatenated together. Finally, the string is cast to a bigint to dispose of the leading zeros. You could wrap this is another CAST to convert the output to a string.

DECLARE @t TABLE (trip_no int);

INSERT @t (trip_no)
VALUES (1100),(1101),(1123),(1124),(1145),(1146),(1181),(1182),
(1187),(1188),(1195),(1196),(7771),(7772),(7773),(7774),(7775),
(7776),(7777),(7778),(8881),(8882);

SELECT trip_no
,CAST(CASE trip_no & 32768 WHEN 32768 THEN '1' ELSE '0' END
+CASE trip_no & 16384 WHEN 16384 THEN '1' ELSE '0' END
+CASE trip_no & 8192 WHEN 8192 THEN '1' ELSE '0' END
+CASE trip_no & 4096 WHEN 4096 THEN '1' ELSE '0' END
+CASE trip_no & 2048 WHEN 2048 THEN '1' ELSE '0' END
+CASE trip_no & 1024 WHEN 1024 THEN '1' ELSE '0' END
+CASE trip_no & 512 WHEN 512 THEN '1' ELSE '0' END
+CASE trip_no & 256 WHEN 256 THEN '1' ELSE '0' END
+CASE trip_no & 128 WHEN 128 THEN '1' ELSE '0' END
+CASE trip_no & 64 WHEN 64 THEN '1' ELSE '0' END
+CASE trip_no & 32 WHEN 32 THEN '1' ELSE '0' END
+CASE trip_no & 16 WHEN 16 THEN '1' ELSE '0' END
+CASE trip_no & 8 WHEN 8 THEN '1' ELSE '0' END
+CASE trip_no & 4 WHEN 4 THEN '1' ELSE '0' END
+CASE trip_no & 2 WHEN 2 THEN '1' ELSE '0' END
+CASE trip_no & 1 WHEN 1 THEN '1' ELSE '0' END AS bigint) AS bin_value
FROM @t;

(Reading all the comments on the original post, this is very similar to the solution in the link provided by Zohar Peled)

answered on Stack Overflow Apr 7, 2016 by

Ed Harper

Here is a fancy way of doing it without using a function:

;WITH N(N)AS 
(SELECT 1 FROM(VALUES(1),(1),(1),(1),(1))M(N)),
tally(N)AS(SELECT ROW_NUMBER()OVER(ORDER BY N.N)FROM N, N a)

SELECT yourtable.num, x.bin
FROM (values(0),(1),(2),(3),(4),(9),(20),(100),(1001)) yourtable(num)
CROSS APPLY(
SELECT  
  REVERSE(( 
        SELECT cast(num / power(2, N - 1) % 2 as char(1))
        FROM tally t1 
        WHERE num >= power(2, N - 1)

        for xml path(''), type 
    ).value('.', 'varchar(max)')) [bin]
) x

Result:

num   bin
0     NULL
1     1
2     10
3     11
4     100
9     1001
20    10100
100   1100100
1001  1111101001

answered on Stack Overflow Apr 7, 2016 by

t-clausen.dk

User contributions licensed under CC BY-SA 3.0