Is there any way to convert an integer (3 in decimal form) to its binary equivalent 11 in SQLite?

I am aware that there are functions in Oracle and MsSQl that can do so, so is there any function that does it or is there any way to convert it inside sqlite?

My table structure;

• col1 primary key int
• col2 not null int <- I want to convert the value of this column into binary an perform a few comparisons for 开发者_如何学Pythona set sequence of binary combinations

(The way I am currently employing is taking the data outside into a datatable in C# and converting it externally, but that's wrong apparently)

The following will give you a lookup table with all the representation you were after. You can join onto this.

begin;

CREATE TABLE LookUpHours(ID integer primary key, representation text);

insert into LookUpHours (id) values ( 0);
insert into LookUpHours (id) values ( 1);
insert into LookUpHours (id) values ( 2);
insert into LookUpHours (id) values ( 3);
insert into LookUpHours (id) values ( 4);
insert into LookUpHours (id) values ( 5);
insert into LookUpHours (id) values ( 6);
insert into LookUpHours (id) values ( 7);
insert into LookUpHours (id) values ( 8);
insert into LookUpHours (id) values ( 9);
insert into LookUpHours (id) values (10);
insert into LookUpHours (id) values (11);
insert into LookUpHours (id) values (12);
insert into LookUpHours (id) values (13);
insert into LookUpHours (id) values (14);
insert into LookUpHours (id) values (15);
insert into LookUpHours (id) values (16);
insert into LookUpHours (id) values (17);
insert into LookUpHours (id) values (18);
insert into LookUpHours (id) values (19);
insert into LookUpHours (id) values (20);
insert into LookUpHours (id) values (21);
insert into LookUpHours (id) values (22);
insert into LookUpHours (id) values (23);
insert into LookUpHours (id) values (24);
insert into LookUpHours (id) values (25);
insert into LookUpHours (id) values (26);
insert into LookUpHours (id) values (27);
insert into LookUpHours (id) values (28);
insert into LookUpHours (id) values (29);
insert into LookUpHours (id) values (30);
insert into LookUpHours (id) values (31);

update LookUpHours set representation =
 case when (id & 16) != 0 then '1' else '0' end ||
 case when (id &  8) != 0 then '1' else '0' end ||
 case when (id &  4) != 0 then '1' else '0' end ||
 case when (id &  2) != 0 then '1' else '0' end ||
 case when (id &  1) != 0 then '1' else '0' end
;

commit;

Resultant table has this data:

ID          representation
----------  --------------
0           00000
1           00001
2           00010
3           00011
4           00100
5           00101
6           00110
7           00111
8           01000
9           01001
10          01010
11          01011
12          01100
13          01101
14          01110
15          01111
16          10000
17          10001
18          10010
19          10011
20          10100
21          10101
22          10110
23          10111
24          11000
25          11001
26          11010
27          11011
28          11100
29          11101
30          11110
31          11111

Thanks alot guys although i got iT ^^ for anyone who has the same problem here is the code.

[SQLiteFunction(Arguments = 1, FuncType = FunctionType.Scalar, Name = "ConvertToBinary")]
        class ConvertToBinary : SQLiteFunction
        {
            public override object Invoke(object[] args)
            {
                int inputNumber = Convert.ToInt16(args[0]);
                return Convert.ToString(inputNumber, 2).PadLeft(5, '0');
            }
        }

You can certainly add functions into SQLite , but you can also get binary representation of numbers just using SQL:

select
    case (a>>15)&1 when 1 then '1' else '0' end ||
    case (a>>14)&1 when 1 then '1' else '0' end ||
    case (a>>13)&1 when 1 then '1' else '0' end ||
    case (a>>12)&1 when 1 then '1' else '0' end ||
    case (a>>11)&1 when 1 then '1' else '0' end ||
    case (a>>10)&1 when 1 then '1' else '0' end ||
    case (a>>9)&1 when 1 then '1' else '0' end ||
    case (a>>8)&1 when 1 then '1' else '0' end ||
    case (a>>7)&1 when 1 then '1' else '0' end ||
    case (a>>6)&1 when 1 then '1' else '0' end ||
    case (a>>5)&1 when 1 then '1' else '0' end ||
    case (a>>4)&1 when 1 then '1' else '0' end ||
    case (a>>3)&1 when 1 then '1' else '0' end ||
    case (a>>2)&1 when 1 then '1' else '0' end ||
    case (a>>1)&1 when 1 then '1' else '0' end ||
    case (a>>0)&1 when 1 then '1' else '0' end
from (select 1023 as a);

for 16 binary digits - easy to extend to more

Use a lookup table with one column for the decimal representation of a number ans one for the binary. Of course, this will only work for as many numbers as you can afford the space to store, and is a lousy way of supporting arithmetic, but it might work for you.