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 2, 2013 7:34 AM
 Forum Newbie Group: General Forum Members Last Login: Wednesday, May 4, 2016 7:35 AM Points: 4, Visits: 59
 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 2, 2013 3:46 PM
 SSC Eights! Group: General Forum Members Last Login: 2 days ago @ 3:29 AM Points: 889, Visits: 862
 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 2, 2013 6:16 PM
 SSC-Forever Group: General Forum Members Last Login: Today @ 8:45 AM Points: 42,066, Visits: 39,447
 chirag ~ (9/2/2013)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 queryPlease see the first "Helpful Link" in my signature line below. You don't have to post a 35KByte binary but a couple of dozen bytes would be helpful. --Jeff Moden"RBAR is pronounced "ree-bar" and is a "Modenism" for "Row-By-Agonizing-Row".First step towards the paradigm shift of writing Set Based code: Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column." Helpful Links:How to post code problemsHow to post performance problems
Post #1490717
 Posted Tuesday, September 3, 2013 1:23 PM
 Ten Centuries Group: General Forum Members Last Login: Monday, November 28, 2016 11:55 AM Points: 1,227, Visits: 3,308
 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`
Post #1491073

 Permissions