How can I do AND BIT OPERATOR between two varbinary fields in SQL

Question

Can someone suggest a good solution to this approach.

I have 2 binary strings (1010101....) of 1024 bits in length

Now I would want to do bit operation (AND) on both to get a value whether it is greater than 0 or not.

Currently I am converting the string to hex(256) and varbinary(128)

So once I have two varbinary I am converting 8 bytes of it to BIGINT on each and doing AND on the two BIG INT.

Can anyone suggest me a better approach in SQL 2012.

Thanks, Bala

Answer 1

After a long discussion I have finally figured out what input data you have. You have a varbinary(128) that is constructed from a binary string (like '1000010010101... ') that is 1024 characters long. SQL Server does not provide a ready function that does such a conversion. I have built one to allow me testing. The following function does such conversion:

CREATE FUNCTION dbo.binStringToBinary(@inputString VARCHAR(1024)) RETURNS VARBINARY(128) AS
BEGIN
    DECLARE @inputBinary VARBINARY(128) = convert(varbinary, '', 2)
    DECLARE @octet int = 1
    DECLARE @len int
    SET @len = Len(@inputString)
    while @octet < @len
    BEGIN
        DECLARE @i int = 0
        DECLARE @Output int = 0
        WHILE(@i < 7) BEGIN
            SET @Output = @Output + POWER(CAST(SUBSTRING(@inputString, @octet + @i, 1) AS int) * 2, 7 - @i)
            SET @i = @i + 1
        END
        SET @Output = @Output + CAST(SUBSTRING(@inputString, @octet + @i, 1) AS int)
        select @inputBinary = @inputBinary + convert(varbinary(1), @Output)
        -- PRINT substring(@inputString, @octet, 8) + ' ' + STR(@Output, 3, 0) + ' ' + convert(varchar(1024), @inputBinary, 2)
        SET @octet = @octet + 8
    END
    RETURN @inputBinary
END

I then have written a function that checks for a bit using the varbinary(128) as an input:

CREATE FUNCTION dbo.[DoBitsMatchFromBinary](@bitToCheck INT,@inputBinary VARBINARY(1024))
RETURNS BIT
AS
BEGIN
    IF @bitToCheck < 1 OR @bitToCheck > 1024
        RETURN 0

    DECLARE @byte int = (@bitToCheck - 1) / 8
    DECLARE @bit int = @bitToCheck - @byte * 8
    DECLARE @bytemask int = POWER(2, 8-@bit)
    SET @byte = @byte + 1

    RETURN CASE WHEN CONVERT(int, CONVERT(binary(1), SUBSTRING(@inputBinary, @byte, 1), 2)) & @bytemask = @bytemask THEN 1 ELSE 0 END
END

As a bonus, I have also included here a function that does the bit check from a input binary string(1024):

CREATE FUNCTION dbo.[DoBitsMatchFromBinString](@bitToCheck INT,@inputString VARCHAR(1024))
RETURNS BIT
AS
BEGIN
    IF @bitToCheck < 1 OR @bitToCheck > 1024
        RETURN 0

    RETURN CASE WHEN SUBSTRING(@inputString, @bitToCheck, 1) = '1' THEN 1 ELSE 0 END
END

Check the SQL fiddle that demonstrates their usage.

DECLARE @inputBinary VARBINARY(128)
select @inputBinary = dbo.binStringToBinary('1010001000101111010111010100001101000100010111101011101010000101101000100010111101011101010000110100010001011110101110101000010110100010001011110101110101000011010001000101111010111010100001011010001000101111010111010100001101000100010111101011101010000101101000100010111101011101010000110100010001011110101110101000010110100010001011110101110101000011010001000101111010111010100001011010001000101111010111010100001101000100010111101011101010000101101000100010111101011101010000110100010001011110101110101000010110100010001011110101110101000011010001000101111010111010100001011010001000101111010111010100001101000100010111101011101010000101101000100010111101011101010000110100010001011110101110101000010110100010001011110101110101000011010001000101111010111010100001011010001000101111010111010100001101000100010111101011101010000101101000100010111101011101010000110100010001011110101110101000010110100010001011110101110101000011010001000101111010111010100001011010001000101111010111010100001101000100010111101011101010000101')
select dbo.[DoBitsMatchFromBinary](1, @inputBinary) bit1,
       dbo.[DoBitsMatchFromBinary](2, @inputBinary) bit2,
       dbo.[DoBitsMatchFromBinary](3, @inputBinary) bit3,
       dbo.[DoBitsMatchFromBinary](4, @inputBinary) bit4,
       dbo.[DoBitsMatchFromBinary](5, @inputBinary) bit5,
       dbo.[DoBitsMatchFromBinary](6, @inputBinary) bit6,
       dbo.[DoBitsMatchFromBinary](7, @inputBinary) bit7,
       dbo.[DoBitsMatchFromBinary](8, @inputBinary) bit8,
       dbo.[DoBitsMatchFromBinary](1017, @inputBinary) bit1017,
       dbo.[DoBitsMatchFromBinary](1018, @inputBinary) bit1018,
       dbo.[DoBitsMatchFromBinary](1019, @inputBinary) bit1019,
       dbo.[DoBitsMatchFromBinary](1020, @inputBinary) bit1020,
       dbo.[DoBitsMatchFromBinary](1021, @inputBinary) bit1021,
       dbo.[DoBitsMatchFromBinary](1022, @inputBinary) bit1022,
       dbo.[DoBitsMatchFromBinary](1023, @inputBinary) bit1023,
       dbo.[DoBitsMatchFromBinary](1024, @inputBinary) bit1024


| bit1 |  bit2 | bit3 |  bit4 |  bit5 |  bit6 | bit7 |  bit8 | bit1017 | bit1018 | bit1019 | bit1020 | bit1021 | bit1022 | bit1023 | bit1024 |
|------|-------|------|-------|-------|-------|------|-------|---------|---------|---------|---------|---------|---------|---------|---------|
| true | false | true | false | false | false | true | false |    true |   false |   false |   false |   false |    true |   false |    true |

Answer 2

Use a numbers table to split the strings. Compare using intersect and check if you get any rows back.

declare @s1 char(1024) = '01100100001',
        @s2 char(1024) = '00000100001';

if exists (
          select substring(@s1, N.Number, 1) as C,
                N.Number
          from Numbers as N
          where N.Number between 1 and 1024 and
                substring(@s1, N.Number, 1) = '1'
          intersect
          select substring(@s2, N.Number, 1) as C,
                N.Number
          from Numbers as N
          where N.Number between 1 and 1024 and
                substring(@s2, N.Number, 1) = '1'
          )
begin
  select 'YES'
end
else
begin
  select 'NO'
end;

The query plan you get will be quite efficient and will terminate early when you have a match:

The top Clustered Index Seek will scan the numbers table from 1 to 1024 with a residual predicate checking if @s1 has a 1 in each position. For each 1 found it will do a seek in the other reference to the numbers table to see if there also is a 1 in that position in @s2. When a match is found the execution will stop.

Answer 3

If your binary numbers can convert to any numeric types this answer can help you.

& (Bitwise AND) (Transact-SQL)
Performs a bitwise logical AND operation between two integer values.

expression & expression

expression
Is any valid expression of any of the data types of the integer data type category, or the bit, or the binary or varbinary data types. expression is treated as a binary number for the bitwise operation.

Note:
In a bitwise operation, only one expression can be of either binary or varbinary data type.

...
You can cast one of expressions to a type like bigint if it's possible.

declare @b1 varbinary(max) = 0x2, @b2 varbinary(max) = 0x3

print Cast(@b1 & cast(@b2 as bigint) as varbinary(max))

Result is:

0x0000000000000002