如何在两种二进制类型之间的 sql server 中进行按位异或? [英] How to do bitwise exclusive OR in sql server between two binary types?

问题描述

根据此链接:位运算符 (Transact-SQL)我们可以在 binary 和 int、smallint、tinyint 之间进行按位运算，反之亦然.

According this link: Bitwise Operators (Transact-SQL) we can do bitwise operation between binary and int, smallint, tinyint or vice versa.

但是如何在 sql server 中两种二进制类型之间进行按位异或?或者，如果这是不可能的，我如何将二进制/varbinary 拆分为单个字节?

But how can I make a bitwise exclusive OR in sql server between two binary types? Or if this is not possible how can I split a binary/varbinary to individual bytes?

我要求这样做的原因是因为我需要对两个大于最大 int 值的数字进行异或.谢谢.

The reason I'm asking for this is because I need to xor two numbers bigger than max int value. Thanks.

推荐答案

代码块中的所有注释

-- variables
declare @vb1 binary(16), @vb2 binary(16), @lo binary(8), @hi binary(8)

-- 2 guids to compare
declare @guid1 uniqueidentifier set @guid1 = '96B4316D-1EA7-4CA3-8D50-FEE8047C1329'
declare @guid2 uniqueidentifier set @guid2 = 'FFFFFFFF-FFFF-FFFF-FFFF-FFFFFFFFFFFF'

-- split every 8 bytes into a binary(8), which is a bigint, the largest size usable with XOR
select @vb1 = @guid1, @vb2 = @guid2

-- xor the high and low parts separately
select @hi = convert(binary(8), substring(@vb1,1,8)) ^ convert(bigint, substring(@vb2,1,8))
select @lo = convert(binary(8), substring(@vb1,9,8)) ^ convert(bigint, substring(@vb2,9,8))

-- the final result, concatenating the bytes using char(8) - binary -> uniqueidentifier
select 'A', @guid1 union all
select 'B', @guid2 union all
select 'A XOR B = ', convert(uniqueidentifier, convert(binary(16),convert(char(8),@hi) + convert(char(8),@lo)))

这篇关于如何在两种二进制类型之间的 sql server 中进行按位异或?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！