Two Varbinary fields' bitwise operation

If you're using 2005 as tags suggest, probably have to use CLR to do it (easily), otherwise, you'll probably have to chop the fields up into int chunks to do it

This is probably lame but it's the first thing I could come up with

-- You need a table of numbers if you don't already have one

select top 8000 id = identity(int,1,1)
into Numbers
from sysobjects s1, sysobjects s2, sysobjects s3



DECLARE @sql varchar(8000)
SELECT @sql = COALESCE(@sql + ') + ',
'Update MyBinaryTable
set Result = Ored
FROM MyBinaryTable
JOIN
(
SELECT Field1='+master.dbo.fn_varbintohexstr(Field1)+',
      Field2='+master.dbo.fn_varbintohexstr(Field2)+',
      Ored = ') + 'convert (varbinary(2), Convert(int, 0x' +
      SubString( master.dbo.fn_varbintohexstr(Field1), 2+ id*4 - 3, 4) + ' ) | ' +  
      'Convert(int, 0x' + SubString( master.dbo.fn_varbintohexstr(Field2), 2+ id*4 - 3, 4) + ' )
      ' FROM Numbers, MyBinaryTable WHERE id*4 <= Len(  master.dbo.fn_varbintohexstr(Field1) )

select @sql = @sql + '
)  ) x
ON x.Field1 = MyBinaryTable.Field1 and x.Field2 = MyBinaryTable.Field2'

exec (@sql )


------------------

That generates and executes this;

Update MyBinaryTable
set Result = Ored
FROM MyBinaryTable
JOIN
(
SELECT Field1=0x420c008418428c01c618638cb3de7a0000000000630c2100000000000000,
      Field2=0x420c008418428c01c618638cb3de7a0000000000630c2100000000000000,
      Ored = convert (varbinary(2), Convert(int, 0x420c ) | Convert(int, 0x420c )
      ) + convert (varbinary(2), Convert(int, 0x0084 ) | Convert(int, 0x0084 )
      ) + convert (varbinary(2), Convert(int, 0x1842 ) | Convert(int, 0x1842 )
      ) + convert (varbinary(2), Convert(int, 0x8c01 ) | Convert(int, 0x8c01 )
      ) + convert (varbinary(2), Convert(int, 0xc618 ) | Convert(int, 0xc618 )
      ) + convert (varbinary(2), Convert(int, 0x638c ) | Convert(int, 0x638c )
      ) + convert (varbinary(2), Convert(int, 0xb3de ) | Convert(int, 0xb3de )
      ) + convert (varbinary(2), Convert(int, 0x7a00 ) | Convert(int, 0x7a00 )
      ) + convert (varbinary(2), Convert(int, 0x0000 ) | Convert(int, 0x0000 )
      ) + convert (varbinary(2), Convert(int, 0x0000 ) | Convert(int, 0x0000 )
      ) + convert (varbinary(2), Convert(int, 0x630c ) | Convert(int, 0x630c )
      ) + convert (varbinary(2), Convert(int, 0x2100 ) | Convert(int, 0x2100 )
      ) + convert (varbinary(2), Convert(int, 0x0000 ) | Convert(int, 0x0000 )
      ) + convert (varbinary(2), Convert(int, 0x0000 ) | Convert(int, 0x0000 )
      ) + convert (varbinary(2), Convert(int, 0x0000 ) | Convert(int, 0x0000 )
      
)  ) x
ON x.Field1 = MyBinaryTable.Field1 and x.Field2 = MyBinaryTable.Field2


Result is

select Result from MyBinaryTable

Result
--------------------------------------------------------------
0xE22D10DE5B6BADB5D65AEBBDB7DE7A0000000000EF3D2100000000000000

thank twoboats for the quick reply. Could you please give me some idea to use CLR to resolve this? I think it won't be a better idea to chop the fields into int and then run the bit operation because at the moment I have more than 11,000 rows in the table.
Thanks again.

Correction, it generates and executes this

Update MyBinaryTable
set Result = Ored
FROM MyBinaryTable
JOIN
(
SELECT Field1=0x420c008418428c01c618638cb3de7a0000000000630c2100000000000000,
      Field2=0xe22d10de5b6badb5d65aebbdb5d67a0000000000ef3d2100000000000000,
      Ored = convert (varbinary(2), Convert(int, 0x420c ) | Convert(int, 0xe22d )
      ) + convert (varbinary(2), Convert(int, 0x0084 ) | Convert(int, 0x10de )
      ) + convert (varbinary(2), Convert(int, 0x1842 ) | Convert(int, 0x5b6b )
      ) + convert (varbinary(2), Convert(int, 0x8c01 ) | Convert(int, 0xadb5 )
      ) + convert (varbinary(2), Convert(int, 0xc618 ) | Convert(int, 0xd65a )
      ) + convert (varbinary(2), Convert(int, 0x638c ) | Convert(int, 0xebbd )
      ) + convert (varbinary(2), Convert(int, 0xb3de ) | Convert(int, 0xb5d6 )
      ) + convert (varbinary(2), Convert(int, 0x7a00 ) | Convert(int, 0x7a00 )
      ) + convert (varbinary(2), Convert(int, 0x0000 ) | Convert(int, 0x0000 )
      ) + convert (varbinary(2), Convert(int, 0x0000 ) | Convert(int, 0x0000 )
      ) + convert (varbinary(2), Convert(int, 0x630c ) | Convert(int, 0xef3d )
      ) + convert (varbinary(2), Convert(int, 0x2100 ) | Convert(int, 0x2100 )
      ) + convert (varbinary(2), Convert(int, 0x0000 ) | Convert(int, 0x0000 )
      ) + convert (varbinary(2), Convert(int, 0x0000 ) | Convert(int, 0x0000 )
      ) + convert (varbinary(2), Convert(int, 0x0000 ) | Convert(int, 0x0000 )
      
)  ) x
ON x.Field1 = MyBinaryTable.Field1 and x.Field2 = MyBinaryTable.Field2


The version I pasted before was from a trial-run where Field1 and Field2 were identical so  I could be sure Result was the same as Field1 or Field2

11,000 rows?

You will have to cursor through them.  Best thing would be to put it in a function but funcations can't execute dynamic SQL

Do you need me to script that or have you got it?

You could also just hard-code the string manipulation statements since there are only 15, then a function could do it.

I have to sleep now but maybe someone else can pick this up.

Thaks kselvia for your wonderful effort. I'm just going through your provided solution. But in my case Field1 can contain duplicate hexadecimal values. Dont you think that while JOINing with the condition  "ON x.Field1 = MyBinaryTable.Field1 and x.Field2 = MyBinaryTable.Field2" multiples rows can be affected?

Actually if you put an index on Result you can adapt my suggestion but it will be slowish

DECLARE @sql varchar(8000)

While (select top 1 x 1 from MyBinaryTable where Result is not null) = 1
begin

SELECT @sql = COALESCE(@sql + ') + ',
'Update MyBinaryTable
set Result = Ored
FROM MyBinaryTable
JOIN
(
SELECT top 1 Field1='+master.dbo.fn_varbintohexstr(Field1)+',
      Field2='+master.dbo.fn_varbintohexstr(Field2)+',
      Ored = ') + 'convert (varbinary(2), Convert(int, 0x' +
      SubString( master.dbo.fn_varbintohexstr(Field1), 2+ id*4 - 3, 4) + ' ) | ' +  
      'Convert(int, 0x' + SubString( master.dbo.fn_varbintohexstr(Field2), 2+ id*4 - 3, 4) + ' )
      ' FROM Numbers, MyBinaryTable WHERE
Result Is Null
AND id*4 <= Len(  master.dbo.fn_varbintohexstr(Field1) )

select @sql = @sql + '
)  ) x
ON x.Field1 = MyBinaryTable.Field1 and x.Field2 = MyBinaryTable.Field2'

exec (@sql )

end

If multiple rows are effected it won't matter because those rows will have the same Result

In fact it's a good thing.

These should help

http://www.simple-talk.com/sql/learn-sql-server/building-my-first-sql-server-2005-clr/

http://www.devsource.com/article2/0,1895,1791676,00.asp

http://msdn2.microsoft.com/en-us/library/ms254956(VS.80).aspx

Ok kselvia, I'll try with your solution now, I think you should go to sleep :)

Just about to.

I suggest you replace master.dbo.fn_varbintohexstr(Fieldn) with variables and use those in the construction of @sql

It's just possible that SQL query engine, in it's infinite wisdom, may call those functions once for each chunk of the hex string. that's 32 times instead of 2.

ie
declare @hex1 varchar(120),
            @hex2 varchar(120)

select @hex1 = master.dbo.fn_varbintohexstr(Field1), @hex2 = master.dbo.fn_varbintohexstr(Field2)
From MybinaryTable where Result is null

Then use @hex1and @hex2 instead of master.dbo.fn_varbintohexstr(Fieldn) to build the dynamic sql

Don't forget to create an index on Result or this will perform very badly

With some modifications to kselvia's solution I've come up with a solution now, I dont know whether this is an optimum solution but for now it seems alright to me. Thanks for all your efforts.
Thanks to twoboats too.
Good luck