sql - 如何在 SQL 中的两个 varbinary 字段之间执行 AND BIT OPERATOR

Question

有人可以针对这种方法提出一个好的解决方案吗？

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

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

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

因此，一旦我有两个 varbinary，我就会将每个 varbinary 的 8 个字节转换为 BIGINT，并对两个 BIG INT 执行 AND 操作。

任何人都可以建议我在 SQL 2012 中使用更好的方法吗？

谢谢，巴拉

Answer 1

经过长时间的讨论，我终于弄清楚了你有什么输入数据。您有一个 varbinary(128)，它是由 1024 个字符长的二进制字符串(例如“1000010010101...”)构造的。 SQL Server 没有提供执行此类转换的现成函数。我已经构建了一个来进行测试。以下函数执行此类转换:

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) 作为输入来检查位:

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) 进行位检查:

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 |