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

Question

Can someone suggest a good solution for this approach.

I have 2 binary strings (1010101 ....) 1024 bits long

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

I am currently converting a string to hex (256) and varbinary (128)

So, as soon as I have two varbinary, I convert 8 bytes to BIGINT on each and do AND on two BIG INTs.

Can anyone suggest me a better approach in SQL 2012.

Thanks Bala

Answer 1

After a long discussion, I finally figured out what kind of input you have. You have varbinary (128), which is built from a binary string (for example, "1000010010101 ...") with a length of 1024 characters. SQL Server does not provide a ready-made function that does such a conversion. I built one to let me test. The following function performs this 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 

Then I wrote a function that checks the bit using varbinary (128) as 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 also included a function here that checks the bits from the 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 out the SQL script that demonstrates their use.

 DECLARE @inputBinary VARBINARY(128) select @inputBinary = dbo.binStringToBinary('') 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 the numbers table to split the lines. Compare with cross and check if any rows are returned.

 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 request plan that you receive will be quite effective and will end earlier when you receive a match:

The top Clustered Index Seek scans a table of numbers from 1 to 1024, checking the residual predicate if @s1 has 1 in each position. For each 1 found, he will look in another reference to a table of numbers to see if there is also 1 at this position in @s2 . When a match is found, execution will stop.

Answer 3

If your binary numbers can be converted to any number types, this answer may help you.

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

 expression & expression 

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

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

...
You can apply one of the expressions to a type of type bigint , if possible.

 declare @b1 varbinary(max) = 0x2, @b2 varbinary(max) = 0x3 print Cast(@b1 & cast(@b2 as bigint) as varbinary(max)) 

Result:

 0x0000000000000002