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.
精彩评论