SQL Server Convert Integer to Binary String

SQL Server Convert integer to binary string

Following could be coded into a function. You would need to trim off leading zeros to meet requirements of your question.

declare @intvalue int
set @intvalue=5

declare @vsresult varchar(64)
declare @inti int
select @inti = 64, @vsresult = ''
while @inti>0
  begin
    select @vsresult=convert(char(1), @intvalue % 2)+@vsresult
    select @intvalue = convert(int, (@intvalue / 2)), @inti=@inti-1
  end
select @vsresult

Convert integer into binary format in SQL Server

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

Converting from integer to binary and back in SQL Server

As I noted in my earlier comment, 2,691,485,888 is larger than what an INT can hold.

This will work:

select CONVERT(BIGINT, CONVERT(BINARY(30), CONVERT(BIGINT, 2691485888)))

How do you convert an integer into its CONDENSED binary equivalent e.g. BINARY(3) in MySQL

You can use HEX() to generate a string with a hexadecimal representation of your integer. Then UNHEX() will convert that string to a binary value:

SET @int_value = 123456;   -- Decimal value

SET @bin_value = UNHEX(HEX(@int_value));

SELECT @int_value, HEX(@bin_value), LENGTH(@bin_value);

results in

@int_value | HEX(@bin_value) | LENGTH(@bin_value)
-----------|-----------------|-------------------
    123456 |          01E240 |                  3

See demo on db<>fiddle

How to convert integer to bit string in Amazon Redshift?

The fact that somebody has written this UDF indicates that there is no in-built capability within Redshift's SQL that can perform this operation.

If you do not want to use UDFs, you could create a lookup table with all binary values and then JOIN to it. This could be faster than running the Python function repeatedly, but I note that the function is coded as STABLE, so it will cache results.

Convert decimal -- binary with T-SQL

This answer can handle bigint

Convert to bit(varchar containing 1 and 0)

DECLARE @input BIGINT = 9223372036854775807

;WITH N(N)AS 
(
  SELECT top 63
    POWER(cast(2 as bigint),
      ROW_NUMBER()over(ORDER BY (SELECT 1))-1)
  FROM
    (VALUES(1),(1),(1),(1))M(a),
    (VALUES(1),(1),(1),(1))L(a),
    (VALUES(1),(1),(1),(1))K(a)
)
SELECT
  COALESCE
  (
    REVERSE
    (
      ( 
        SELECT CAST(@input/N%2 as CHAR(1))
        FROM N 
        WHERE N <= @input
        for xml path(''), type 
      ).value('.', 'varchar(max)')
    )
    , '0'
  )

Result:

111111111111111111111111111111111111111111111111111111111111111

Convert varchar containing bit values to bigint

DECLARE @input varchar(max) = 
  '111111111111111111111111111111111111111111111111111111111111111'

;WITH N(V) AS 
(
  SELECT
    ROW_NUMBER()over(ORDER BY (SELECT 1))
  FROM
    (VALUES(1),(1),(1),(1))M(a),
    (VALUES(1),(1),(1),(1))L(a),
    (VALUES(1),(1),(1),(1))K(a)
)
SELECT SUM(SUBSTRING(REVERSE(@input),V,1)*POWER(CAST(2 as BIGINT), V-1))
FROM   N
WHERE  V <= LEN(@input)

Result:

9223372036854775807

SQL Server : how to convert binary back to int

Query returns hexadecimal number (0x... - it is hexadecimal) as its bit mask

CREATE TABLE #Temp(
    Test    VARBINARY(2)
)

INSERT #Temp
VALUES
    (0x1001),
    (0x3001),
    (0x5000),
    (0x6000),
    (0xf000),
    (0xf250)

SELECT *, REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(
    CONVERT(VARCHAR(32), Test, 2)
    , '0', '0000')
    , '1', '0001')
    , '2', '0010')
    , '3', '0011')
    , '4', '0100')
    , '5', '0101')
    , '6', '0110')
    , '7', '0111')
    , '8', '1000')
    , '9', '1001')
    , 'a', '1010')
    , 'b', '1011')
    , 'c', '1100')
    , 'd', '1101')
    , 'e', '1110')
    , 'f', '1111')
FROM #Temp

DROP TABLE #Temp

SQL Server convert varbinary(16) to binary text

@value % 2 and @value / 2 is doing an implicit conversion.

select @value = convert(int, (@value / 2)) is doing an explicit conversion to int so here you are getting a negative int for the values stored in varbinary(16) that after the division converted to bigint is larger than 2,147,483,647.
% for a negative int will give you a -1.

I do not think it is possible to convert varbinary(16) to binary using % and /. They only work on int/bigint etc.

Here is a conversion routine that works for positive bigint values. I do not know what representation you would expect for negative bigint values.
Convert your varbinary(16) field to bigint in the call to the function and perhaps it does what you want.
I am sure it does not work for all possible values that you can store in a varbinary(16) field.

create function BigIntToBin (@v bigint)
returns varchar(256)
as
begin
    declare @res varchar(256)
    declare @i int

    set @i = 128
    set @res = ''

    while @i > 0
    begin
        if @v % 2 = 0
            set @res = '0' + @res
        else    
            set @res = '1' + @res
        set @v = @v / 2
        set @i = @i - 1
    end
    return @res
end

---

Related Topics