Binary comparison - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

Binary comparison

Author

Topic

chiragvm
Yak Posting Veteran

65 Posts

Posted - 2013-08-26 : 02:33:24

Hi to all

is this possible to compare varbinary(max) with varbinary(max)

i have a table where store a varbinary(max)data Length of data is (35040)
value contans only 0x00 or 0x01 i am generating binary value as below query and store in table

Declare @mybyte varbinary(max)

Select @mybyte = COALESCE(@mybyte , 0x) + Cast ((Case When Sum(A.[book]) >= 1 then 1 else 0 end)as binary(1)) from (
Select A.Dates, A.Minutes, c.Cid,(case when A.Minutes between (Datepart(hour,C.StartTime ) * 60 + datepart(minute,C.StartTime )) and (Datepart(hour,C.EndTime ) * 60 + datepart(minute,C.EndTime )) then 1 else 0 end) as [book]
from (select C.CalId,C.Dates,C.Days,C.Months,C.Years,C.Weekdays,C.WeekNum,T.Hours,T.Min,T.Minutes from Calender C, Clock T) A
Left outer join Candidate C on A.[Dates] = C.[Date] And c.Cid = 1 where A.Years = 2013) A
Group by A.Dates, A.Minutes order by Dates

value1 = 010101000100010101010000000000000100..00(35040)
value2 = 010101000000010101010000000010001000..00(35040)
Result = 010101000000010101010000000000000000..00(35040)

need to compare value1 with value2 and generaete result
need to compare byte to byte
Compare when value2 byte is 0x01 then compare with value1 if value1 has a same value then appand in Result with 0x01 else 0x00

-------------
Chirag
India
Sr. Sw.Engineer

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-08-26 : 08:45:59

What you need is a bitwise and operator, but SQL bitwise and operator does not accept binaries on both sides. http://technet.microsoft.com/en-us/library/ms176122.aspx

The only thing I can think of is to use an intermediate data type in some clever way. Here is a simple example - not sure if this is the way to go about it, and even if it is how you would extend it to a large number of characters. Hopefully someone will post a better solution and shoot down my hare-brained idea

DECLARE @x VARBINARY(MAX);
DECLARE @y INT;
DECLARE @z VARBINARY(MAX)
SET @x = 0x10100111;
SET @y = 0x10100011;
SET @z = 0x10100000;

SELECT CAST((@x&@y)&@z AS VARBINARY(MAX))

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources