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

Question

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

my table is of structure :

col 1primary key int| col2 not null int <------- i want to convert the value of this column into binary an perform a few comparisons for a 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 thats wrong apparently)

Answer 1

Hmm, no answers after 2 hours, here's one -- use a lookup table with one column for the decimal representation of a number, 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.

Answer 2

well it is a nice solution since i am only concerned with numbers from 1-31 , but the main problem lies in the fact that i need to employ the binary sequence as follows.

00001 - implies absent first hr , 00011- absent 1st n 2nd hr so a select statement that checks students absent for 2nd hr wld take alot of values and using a lookup table i wont exactly be able to tell which students fourth value is one ( 00010).

public string ConvertToBinary(int num)
        {
            return Convert.ToString(num, 2).PadLeft(5, '0');
        }

is dere any way to employ this function into sqlite??

Answer 3

You can certainly add functions into SQLite (http://www.sqlite.org/c3ref/create_function.html), 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

Answer 4

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

Answer 5

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');
            }
        }