SQL Server Convert integer to binary string

Question

I was wondering if there was an easy way in SQL to convert an integer to its binary representation and then store it as a varchar.

For example 5 would be converted to "101" and stored as a varchar.

Answer 1

On SQL Server, you can try something like the sample below:

DECLARE  @Int int = 321

SELECT @Int
,CONCAT
(CAST(@Int & power(2,15)    AS bit)
,CAST(@Int & power(2,14)    AS bit)
,CAST(@Int & power(2,13)    AS bit)
,CAST(@Int & power(2,12)    AS bit)
,CAST(@Int & power(2,11)    AS bit)
,CAST(@Int & power(2,10)    AS bit)
,CAST(@Int & power(2,9)     AS bit)
,CAST(@Int & power(2,8)     AS bit)
,CAST(@Int & power(2,7)     AS bit)
,CAST(@Int & power(2,6)     AS bit)
,CAST(@Int & power(2,5)     AS bit)
,CAST(@Int & power(2,4)     AS bit)
,CAST(@Int & power(2,3)     AS bit)
,CAST(@Int & power(2,2)     AS bit)
,CAST(@Int & power(2,1)     AS bit)
,CAST(@Int & power(2,0)     AS bit) ) AS BitString

,CAST(@Int & power(2,15)    AS bit) AS BIT15
,CAST(@Int & power(2,14)    AS bit) AS BIT14
,CAST(@Int & power(2,13)    AS bit) AS BIT13
,CAST(@Int & power(2,12)    AS bit) AS BIT12
,CAST(@Int & power(2,11)    AS bit) AS BIT11
,CAST(@Int & power(2,10)    AS bit) AS BIT10
,CAST(@Int & power(2,9)     AS bit) AS BIT9 
,CAST(@Int & power(2,8)     AS bit) AS BIT8 
,CAST(@Int & power(2,7)     AS bit) AS BIT7 
,CAST(@Int & power(2,6)     AS bit) AS BIT6 
,CAST(@Int & power(2,5)     AS bit) AS BIT5 
,CAST(@Int & power(2,4)     AS bit) AS BIT4 
,CAST(@Int & power(2,3)     AS bit) AS BIT3 
,CAST(@Int & power(2,2)     AS bit) AS BIT2 
,CAST(@Int & power(2,1)     AS bit) AS BIT1 
,CAST(@Int & power(2,0)     AS bit) AS BIT0  

Answer 2

Actually this is REALLY SIMPLE using plain old SQL. Just use bitwise ANDs. I was a bit amazed that there wasn't a simple solution posted online (that didn't invovled UDFs). In my case I really wanted to check if bits were on or off (the data is coming from dotnet eNums).

Accordingly here is an example that will give you seperately and together - bit values and binary string (the big union is just a hacky way of producing numbers that will work accross DBs:

    select t.Number
    , cast(t.Number & 64 as bit) as bit7
    , cast(t.Number & 32 as bit) as bit6
    , cast(t.Number & 16 as bit) as bit5
    , cast(t.Number & 8 as bit) as bit4
    , cast(t.Number & 4 as bit) as bit3
    , cast(t.Number & 2 as bit)  as bit2
    ,cast(t.Number & 1 as bit) as bit1

    , cast(cast(t.Number & 64 as bit) as CHAR(1)) 
    +cast( cast(t.Number & 32 as bit) as CHAR(1))
    +cast( cast(t.Number & 16 as bit)  as CHAR(1))
    +cast( cast(t.Number & 8 as bit)  as CHAR(1))
    +cast( cast(t.Number & 4 as bit)  as CHAR(1))
    +cast( cast(t.Number & 2 as bit)   as CHAR(1))
    +cast(cast(t.Number & 1 as bit)  as CHAR(1)) as binary_string
    --to explicitly answer the question, on MSSQL without using REGEXP (which would make it simple)
    ,SUBSTRING(cast(cast(t.Number & 64 as bit) as CHAR(1)) 
                    +cast( cast(t.Number & 32 as bit) as CHAR(1))
                    +cast( cast(t.Number & 16 as bit)  as CHAR(1))
                    +cast( cast(t.Number & 8 as bit)  as CHAR(1))
                    +cast( cast(t.Number & 4 as bit)  as CHAR(1))
                    +cast( cast(t.Number & 2 as bit)   as CHAR(1))
                    +cast(cast(t.Number & 1 as bit)  as CHAR(1))
                    ,
                    PATINDEX('%1%', cast(cast(t.Number & 64 as bit) as CHAR(1)) 
                                        +cast( cast(t.Number & 32 as bit) as CHAR(1))
                                        +cast( cast(t.Number & 16 as bit)  as CHAR(1))
                                        +cast( cast(t.Number & 8 as bit)  as CHAR(1))
                                        +cast( cast(t.Number & 4 as bit)  as CHAR(1))
                                        +cast( cast(t.Number & 2 as bit)   as CHAR(1))
                                        +cast(cast(t.Number & 1 as bit)  as CHAR(1)  )
                    )
,99)


from (select 1 as Number union all select 2 union all select 3 union all select 4 union all select 5 union all select 6 
    union all select 7 union all select 8 union all select 9 union all select 10) as t

Produces this result:

num  bit7 bit6 bit5 bit4 bit3 bit2 bit1 binary_string   binary_string_trimmed 
1    0    0    0    0    0    0    1    0000001         1
2    0    0    0    0    0    1    0    0000010         10
3    0    0    0    0    0    1    1    0000011         11
4    0    0    0    1    0    0    0    0000100         100
5    0    0    0    0    1    0    1    0000101         101
6    0    0    0    0    1    1    0    0000110         110
7    0    0    0    0    1    1    1    0000111         111
8    0    0    0    1    0    0    0    0001000         1000
9    0    0    0    1    0    0    1    0001001         1001
10   0    0    0    1    0    1    0    0001010         1010

Answer 3

I believe that this method simplifies a lot of the other ideas that others have presented. It uses bitwise arithmetic along with the FOR XML trick with a CTE to generate the binary digits.

DECLARE @my_int INT = 5

;WITH CTE_Binary AS
(
    SELECT 1 AS seq, 1 AS val
    UNION ALL
    SELECT seq + 1 AS seq, power(2, seq)
    FROM CTE_Binary
    WHERE
        seq < 8
)
SELECT
(
    SELECT
        CAST(CASE WHEN B2.seq IS NOT NULL THEN 1 ELSE 0 END AS CHAR(1))
    FROM
        CTE_Binary B1
    LEFT OUTER JOIN CTE_Binary B2 ON
        B2.seq = B1.seq AND
        @my_int & B2.val = B2.val
    ORDER BY
        B1.seq DESC
    FOR XML PATH('')
) AS val

Answer 4

with t as (select * from (values (0),(1)) as t(c)),

t0 as (table t),
t1 as (table t),
t2 as (table t),
t3 as (table t),
t4 as (table t),
t5 as (table t),
t6 as (table t),
t7 as (table t),
t8 as (table t),
t9 as (table t),
ta as (table t),
tb as (table t),
tc as (table t),
td as (table t),
te as (table t),
tf as (table t)

select  '' || t0.c || t1.c || t2.c || t3.c || t4.c || t5.c || t6.c || t7.c || t8.c || t9.c || ta.c || tb.c || tc.c || td.c || te.c || tf.c as n
from  t0,t1,t2,t3,t4,t5,t6,t7,t8,t9,ta,tb,tc,td,te,tf
order by n 

limit 1 offset 5

Standard SQL (tested in PostgreSQL).

Answer 5

declare @intVal Int 
set @intVal = power(2,12)+ power(2,5) + power(2,1);
With ComputeBin (IntVal, BinVal,FinalBin)
As
    (
    Select @IntVal IntVal, @intVal %2 BinVal , convert(nvarchar(max),(@intVal %2 ))     FinalBin
    Union all
    Select IntVal /2, (IntVal /2) %2, convert(nvarchar(max),(IntVal /2) %2) + FinalBin     FinalBin
    From ComputeBin
    Where IntVal /2 > 0
)
select FinalBin from ComputeBin where intval = ( select min(intval) from ComputeBin);

Answer 6

Here's a bit of a change to the accepted answer from Sean, since I found it limiting to only allow a hardcoded number of digits in the output. In my daily use, I find it more useful to either get only up to the highest 1 digit, or specify how many digits I'm expecting back. It will automatically pad the side with 0s, so that it lines up to 8, 16, or whatever number of bits you want.

Create function f_DecimalToBinaryString
    (
        @Dec int,
        @MaxLength int = null
    )
Returns varchar(max)
as Begin

    Declare @BinStr varchar(max) = '';

    -- Perform the translation from Dec to Bin
    While @Dec > 0 Begin

        Set @BinStr = Convert(char(1), @Dec % 2) + @BinStr;
        Set @Dec = Convert(int, @Dec /2);

    End;

    -- Either pad or trim the output to match the number of digits specified.
    If (@MaxLength is not null) Begin
        If @MaxLength <= Len(@BinStr) Begin -- Trim down
            Set @BinStr = SubString(@BinStr, Len(@BinStr) - (@MaxLength - 1), @MaxLength);
        End Else Begin -- Pad up
            Set @BinStr = Replicate('0', @MaxLength - Len(@BinStr)) + @BinStr;
        End;
    End;

    Return @BinStr;

End;