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

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