0

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:

trip_no
1100
1101
1123
1124
1145
1146
1181
1182
1187
1188
1195
1196
7771
7772
7773
7774
7775
7776
7777
7778
8881
8882

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:

trip_no 
1100    L
1101    M
1123    c
1124    d
1145    y
1146    z
1181    ќ
1182    ћ
1187    Ј
1188    ¤
1195    «
1196    ¬
7771    [
7772    \
7773    ]
7774    ^
7775    _
7776    `
7777    a
7778    b
8881    "±
8882    "І

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".

Community
  • 1
  • 1
PURWU
  • 397
  • 1
  • 8
  • 22
  • 1
    Possible duplicate of [SQL Server Convert integer to binary string](http://stackoverflow.com/questions/127116/sql-server-convert-integer-to-binary-string) – Rick S Apr 07 '16 at 14:29
  • that is not 'a leading zero' that is notation for Exadecimal and the prefix is "0x". There are two things here to keep in mind A) the actual data type of your data B) the visual representation of that data by SSMS. The output display does not dictates the data type – Ricardo C Apr 07 '16 at 14:34
  • The #1 solution you provided in the link gave an executable query to convert. But I was wondering is it possible to convert via one statement. The second one seemed too complex for this purpose. @RickS – PURWU Apr 07 '16 at 14:35
  • the data type of the original table is `INT`. I seriously have no idea of the data type presented in the desired output @RicardoC – PURWU Apr 07 '16 at 14:38
  • What is expected format output – Jaydip Jadhav Apr 07 '16 at 14:40
  • Since the question mentioned that the desired output is `its binary number representation (without leading zeroes)`, I guess it is either `INT` or `VARCHAR`. @JaydipJ – PURWU Apr 07 '16 at 14:43
  • @furin_kazan What I am trying to tell you is that 0x0000044C is the BINARY data represented on your screen as an exadecimal number by SSMS, The data is still binary, regardless how it is presented. – Ricardo C Apr 07 '16 at 15:03
  • 1
    [Read this blog post on this subject](http://ariely.info/Blog/tabid/83/EntryId/169/T-SQL-Converting-between-Decimal-Binary-and-Hexadecimal.aspx). It's simpler then you think. – Zohar Peled Apr 07 '16 at 15:06
  • @furin_kazan Everybody here is working on producing a STRING. A string is not a binary number, even if it looks like it. IF that is what you need, then fine. It should be used for aesthetics only since no math can be applied to a string. – Ricardo C Apr 07 '16 at 15:08

2 Answers2

4

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
t-clausen.dk
  • 43,517
  • 12
  • 59
  • 92
  • it works. thanks for the sharing, although I think it does take me sometime to digest your solution. thx anyway. – PURWU Apr 07 '16 at 15:34
4

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)

Ed Harper
  • 21,127
  • 4
  • 54
  • 80