Recent PostsRecent Posts Popular TopicsPopular Topics
 Home Search Members Calendar Who's On

 Match Varbinary(max) with another Varbinary(max) Rate Topic Display Mode Topic Options
Author
 Message
 Posted Monday, September 02, 2013 7:34 AM
 Forum Newbie Group: General Forum Members Last Login: Friday, November 29, 2013 5:54 AM Points: 1, Visits: 23
 i have a table with one binary column. that contains 0x00010100000101010101......00 column datalength is 35040, i am prepare this data like@Jobbyte = COALESCE(@Jobbyte , 0x) + Cast ((Case When Sum(A.bit) >= 1 then 1 else 0 end)as binary(1))i have to compare this binary data with another binary data and get the matching binary count. both the binary data has equal length of dataPlease look below image, their is 2 binary data Binary1 and Binary2 i want to compare both binary data and get Sum of matching binary data. only one condition is when binary2's single bit is 0x01Last Row is indicate 0 = No Match, 1 = Match and last column is Sum of Last row (4) Please suggest me how can i compare , it's better if you can post a query
Post #1490622
 Posted Monday, September 02, 2013 3:46 PM
 Right there with Babe Group: General Forum Members Last Login: 2 days ago @ 4:01 PM Points: 715, Visits: 572
 My head spins...If I understand it correctly, you have a table with bit values and you want to make some sort of comparison, and you think that a varbinary(MAX) is not what you need. But I'd say that most likely it isn't. Why don't you present your original problem instead, and might be able to find a solution. A tip: you help yourself enormously if you post:1) CREATE TABLE statements for your table(s).2) INSERT statements with sample data.3) The desired result given the sample.4) A short description of the business rules.This way can copy the script you post to develop a tested solution. Erland Sommarskog, SQL Server MVP, www.sommarskog.se
Post #1490695
 Posted Monday, September 02, 2013 6:16 PM
 SSC-Dedicated Group: General Forum Members Last Login: Yesterday @ 9:51 PM Points: 34,581, Visits: 28,764
 HiTo 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`