I have an int type data (an INT number like 15). To convert this into a 5 bits boolean array it becomes [1,1,1,1,0]
I have tried
SELECT CAST(15 AS binary(5))
But I get a hex value instead:
0x0000000F
I would like to be able to select the corresponding bits, so it needs to be a boolean array.
Thanks for any help
Two-step process: First covert Int to Binary Stream and then convert that to varchar and add zeros after the occurrence of each character. As you always want the length to be 5, I have included that part too.
This will help you:
declare @intvalue int
set @intvalue=15
--Convert Int to Binary Stream
declare @result varchar(64)
declare @i int
select @i = 64, @result = ''
while @i>0
begin
select @result=convert(char(1), @intvalue % 2)+@result
select @intvalue = convert(int, (@intvalue / 2)), @i=@i-1
end
DECLARE @Char VARCHAR(800) = RIGHT(@result,5) --TRIM leading zeros and store binary stream as varchar (length of 5)
--Add comma(,) after every character
DECLARE @TotalChar INT = LEN(@Char)
DECLARE @Counter INT = @TotalChar
WHILE @Counter >= 1
BEGIN
IF @Counter % 1 = 0 AND @Counter + 1 <= @TotalChar
BEGIN
SET @Char = STUFF(@Char, @Counter + 1, 0, ', ')
END
SET @Counter = @Counter - 1
END
SELECT @Char --Final output
SQL Server is not built for bit-wise queries, but there are bitwise operators:
If you need this more often you might create a Bitmap-table and keep this within your database. Doesn't need much space and might be handsome...
DECLARE @bitmaptable TABLE(BitNr INT,IntValue INT,Bitmap BINARY(4),InvertedBitMap BINARY(4));
WITH Numbers AS
(
SELECT TOP 33 CAST(ROW_NUMBER() OVER(ORDER BY (SELECT NULL))-1 AS bigint) AS Nr
FROM master..spt_values
)
INSERT INTO @bitmaptable(BitNr,IntValue,BitMap,InvertedBitMap)
SELECT CASE WHEN Nr=32 THEN -1 ELSE Nr END
,CAST(CAST(pwd2 AS BINARY(4)) AS int)
,CAST(pwd2 AS BINARY(4))
,pwd2 ^ 0xFFFFFFFF
FROM Numbers
CROSS APPLY(SELECT POWER(CAST(2 AS BIGINT),Nr) AS pwd2) AS A;
The result (the last row with -1 is the nothing set bitmap)
+-------+-------------+------------+----------------+
| BitNr | IntValue | Bitmap | InvertedBitMap |
+-------+-------------+------------+----------------+
| 0 | 1 | 0x00000001 | 0xFFFFFFFE |
+-------+-------------+------------+----------------+
| 1 | 2 | 0x00000002 | 0xFFFFFFFD |
+-------+-------------+------------+----------------+
| 2 | 4 | 0x00000004 | 0xFFFFFFFB |
+-------+-------------+------------+----------------+
| 3 | 8 | 0x00000008 | 0xFFFFFFF7 |
+-------+-------------+------------+----------------+
| 4 | 16 | 0x00000010 | 0xFFFFFFEF |
+-------+-------------+------------+----------------+
| 5 | 32 | 0x00000020 | 0xFFFFFFDF |
+-------+-------------+------------+----------------+
| 6 | 64 | 0x00000040 | 0xFFFFFFBF |
+-------+-------------+------------+----------------+
| 7 | 128 | 0x00000080 | 0xFFFFFF7F |
+-------+-------------+------------+----------------+
| 8 | 256 | 0x00000100 | 0xFFFFFEFF |
+-------+-------------+------------+----------------+
| 9 | 512 | 0x00000200 | 0xFFFFFDFF |
+-------+-------------+------------+----------------+
| 10 | 1024 | 0x00000400 | 0xFFFFFBFF |
+-------+-------------+------------+----------------+
| 11 | 2048 | 0x00000800 | 0xFFFFF7FF |
+-------+-------------+------------+----------------+
| 12 | 4096 | 0x00001000 | 0xFFFFEFFF |
+-------+-------------+------------+----------------+
| 13 | 8192 | 0x00002000 | 0xFFFFDFFF |
+-------+-------------+------------+----------------+
| 14 | 16384 | 0x00004000 | 0xFFFFBFFF |
+-------+-------------+------------+----------------+
| 15 | 32768 | 0x00008000 | 0xFFFF7FFF |
+-------+-------------+------------+----------------+
| 16 | 65536 | 0x00010000 | 0xFFFEFFFF |
+-------+-------------+------------+----------------+
| 17 | 131072 | 0x00020000 | 0xFFFDFFFF |
+-------+-------------+------------+----------------+
| 18 | 262144 | 0x00040000 | 0xFFFBFFFF |
+-------+-------------+------------+----------------+
| 19 | 524288 | 0x00080000 | 0xFFF7FFFF |
+-------+-------------+------------+----------------+
| 20 | 1048576 | 0x00100000 | 0xFFEFFFFF |
+-------+-------------+------------+----------------+
| 21 | 2097152 | 0x00200000 | 0xFFDFFFFF |
+-------+-------------+------------+----------------+
| 22 | 4194304 | 0x00400000 | 0xFFBFFFFF |
+-------+-------------+------------+----------------+
| 23 | 8388608 | 0x00800000 | 0xFF7FFFFF |
+-------+-------------+------------+----------------+
| 24 | 16777216 | 0x01000000 | 0xFEFFFFFF |
+-------+-------------+------------+----------------+
| 25 | 33554432 | 0x02000000 | 0xFDFFFFFF |
+-------+-------------+------------+----------------+
| 26 | 67108864 | 0x04000000 | 0xFBFFFFFF |
+-------+-------------+------------+----------------+
| 27 | 134217728 | 0x08000000 | 0xF7FFFFFF |
+-------+-------------+------------+----------------+
| 28 | 268435456 | 0x10000000 | 0xEFFFFFFF |
+-------+-------------+------------+----------------+
| 29 | 536870912 | 0x20000000 | 0xDFFFFFFF |
+-------+-------------+------------+----------------+
| 30 | 1073741824 | 0x40000000 | 0xBFFFFFFF |
+-------+-------------+------------+----------------+
| 31 | -2147483648 | 0x80000000 | 0x7FFFFFFF |
+-------+-------------+------------+----------------+
| -1 | 0 | 0x00000000 | 0xFFFFFFFF |
+-------+-------------+------------+----------------+
Do solve your actual issue you might do something this:
DECLARE @yourNumber INT=15
SELECT bm.BitNr
,CASE WHEN bm.Bitmap & @yourNumber>0 THEN 1 ELSE 0 END
FROM @bitmaptable AS bm
Which returns
0 1
1 1
2 1
3 1
4 0
5 0
6 0
7 0
You could try approach using CTE:
declare @i int = 13
;with cte as(
select 0 [n], @i % 2 [bits], @i / 2 [int]
union all
select [n] + 1, [int] % 2, [int] / 2 from cte
where [int] / 2 > 0
), cte2 as (
select [n] [relevance], [bits] from cte
union all
select MAX([n]) + 1, 1 from cte
)
select * from cte2
--if you'd like to re-create number
--select sum(POWER(2, relevance) * bits) from cte2
This will result in talbe containing bits. The relevance is according to n column, the biggest n corresponds to most relevant (left-most) bit.