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