Search code examples
sqlt-sqlbit-manipulationxor

How to compare binary fields longer than 64 bits

I have a new database bound application. I have a TableA with Column1 as binary(128) that always contains only 1 flag raised. I have another TableB that contains Column5 binary(128) that contains bitmasks that when applied to TableA.Column1 produces all records that have at least one flag raised. Example

TableA
Column1   Record
0000      Product1
0001      Product2
0010      Product3
0100      Product4
1000      Product5

TableB
Column5       Column6
0110          'Cool products'
0101          'Fun products'

When applied

declare @mask as int
select @mask = CAST(TableB.Column5 as int) from TableB WHERE TableB.Column6 = 'Cool products'

select *
from TableA as t
WHERE t.Column1 & @mask <> 0

procuces Product3 and Product4. Everything works as intended. I chose binary for simplicity of flag assignment and fanstastic performance and flexibility of customizing search masks. The problem is that bigint is 64 bit long and I need to apply masks to 128 bit long column so how do I chieve that same functionality as presented above?

Solution

  • something like

    create table #a
(
X varbinary(24) null
)
create table #k
(
K varbinary(24) null
)

select X,K
abs(substring(X,1,8)&cast(substring(K,1,8) as bigint))
+abs(substring(X,9,8)&cast(substring(K,9,8) as bigint))
+abs(substring(X,17,8)&cast(substring(K,17,8) as bigint)) as result
 from #a,#K

    but I discourage to do such sort of manipulations to organize your taxonomy. I think it would be much better to use relations and common SQL technique.
    I haven't seen any case in real life, when such technique results into performance gain.