Home Forums SQL Server 2008 SQL Server 2008 - General Match Varbinary(max) with another Varbinary(max) RE: Match Varbinary(max) with another Varbinary(max)

  • September 3, 2013 at 1:23 pm

    #1647132

    Hi

    To me it looks like you want to do a & (bitwise AND) on the binaries. Unfortunately for the length of the binaries that you are indicating this will not work in SQL Server. Splitting into chunks of 8 appears to work OK, but this would need to be thoroughly tested.

    Also this looks really ugly and there is bound to be a better way.

    with sampleBins as (

    select

    bin1 = cast(1 as binary(1)) + cast(1 as binary(1)) + cast(1 as binary(1)) + cast(0 as binary(1)) + cast(0 as binary(1)) + cast(0 as binary(1)) + cast(1 as binary(1)) + cast(1 as binary(1))

    + cast(1 as binary(1)) + cast(1 as binary(1)) + cast(1 as binary(1)) + cast(0 as binary(1)) + cast(0 as binary(1)) + cast(0 as binary(1)) + cast(1 as binary(1)) + cast(1 as binary(1))

    + cast(1 as binary(1)) + cast(1 as binary(1)) + cast(1 as binary(1)) + cast(0 as binary(1)) + cast(0 as binary(1)) + cast(0 as binary(1)) + cast(1 as binary(1)) + cast(1 as binary(1))

    + cast(1 as binary(1)) + cast(1 as binary(1)) + cast(1 as binary(1)) + cast(0 as binary(1)) + cast(0 as binary(1)) + cast(0 as binary(1)) + cast(1 as binary(1)) + cast(1 as binary(1))

    + cast(1 as binary(1)) + cast(1 as binary(1)) + cast(1 as binary(1)) + cast(0 as binary(1))

    ,

    bin2 = cast(0 as binary(1)) + cast(1 as binary(1)) + cast(1 as binary(1)) + cast(0 as binary(1)) + cast(0 as binary(1)) + cast(1 as binary(1)) + cast(1 as binary(1)) + cast(1 as binary(1))

    + cast(0 as binary(1)) + cast(1 as binary(1)) + cast(1 as binary(1)) + cast(0 as binary(1)) + cast(0 as binary(1)) + cast(1 as binary(1)) + cast(1 as binary(1)) + cast(1 as binary(1))

    + cast(0 as binary(1)) + cast(1 as binary(1)) + cast(1 as binary(1)) + cast(0 as binary(1)) + cast(0 as binary(1)) + cast(1 as binary(1)) + cast(1 as binary(1)) + cast(1 as binary(1))

    + cast(0 as binary(1)) + cast(1 as binary(1)) + cast(1 as binary(1)) + cast(0 as binary(1)) + cast(0 as binary(1)) + cast(1 as binary(1)) + cast(1 as binary(1)) + cast(1 as binary(1))

    + cast(0 as binary(1)) + cast(1 as binary(1)) + cast(1 as binary(1)) + cast(0 as binary(1))

    )

    select

    SUM(LEN(REPLACE(REPLACE(

    CONVERT(varchar(MAX),

    CAST(

    CAST(SUBSTRING(b.bin1,((N - 1)*8)+1,8) as bigint)

    &

    CAST(SUBSTRING(b.bin2,((N - 1)*8)+1,8) as bigint)

    as varbinary(MAX))

    ,1)

    ,'0',''),'x','')))

    from sampleBins b

    cross apply (select top (((datalength(b.bin1) - 1) / 8) + 1) N From Tally) t