经过长时间的讨论,我终于弄清楚了你有什么输入数据。您有一个 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小提琴 http://sqlfiddle.com/#!6/be81a/4这展示了它们的用法。
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 |