T-SQL convert INT to BOOLEAN array

Question

T-SQL convert INT to BOOLEAN array

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

arrays

sql-server

tsql

int

boolean

asked on Stack Overflow Apr 23, 2018 by

greenheadprogrammer • edited Apr 23, 2018 by

marc_s

3 Answers

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

answered on Stack Overflow Apr 23, 2018 by

DEEPAK LAKHOTIA

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.

answered on Stack Overflow Apr 23, 2018 by

Michał Turczyn • edited Apr 23, 2018 by

Michał Turczyn

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

answered on Stack Overflow Apr 23, 2018 by

Shnugo

User contributions licensed under CC BY-SA 3.0