如何在 SQL 中的两个 varbinary 字段之间执行 AND BIT OPERATOR [英] How can I do AND BIT OPERATOR between two varbinary fields in SQL

问题描述

有人可以为这种方法提出一个好的解决方案吗.

Can someone suggest a good solution to this approach.

我有 2 个长度为 1024 位的二进制字符串 (1010101....)

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

现在我想对两者进行位运算 (AND) 以获得一个值，无论它是否大于 0.

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

目前我正在将字符串转换为 hex(256) 和 varbinary(128)

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

因此，一旦我有两个 varbinary，我就会将其中的 8 个字节转换为 BIGINT，并对两个 BIGINT 执行 AND.

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.

谁能建议我在 SQL 2012 中使用更好的方法.

Can anyone suggest me a better approach in SQL 2012.

谢谢，巴拉

推荐答案

经过长时间的讨论，我终于弄清楚您有哪些输入数据.您有一个 varbinary(128)，它由一个 1024 个字符长的二进制字符串(如 '1000010010101...')构成.SQL Server 不提供执行此类转换的就绪函数.我已经建立了一个允许我进行测试.以下函数执行此类转换:

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

然后我编写了一个函数，它使用 varbinary(128) 作为输入来检查位:

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

作为奖励，我还在此处包含了一个从输入二进制字符串 (1024) 执行位检查的函数:

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

查看演示其用法的 SQL fiddle.

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 |

这篇关于如何在 SQL 中的两个 varbinary 字段之间执行 AND BIT OPERATOR的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！