• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1196
  • Last Modified:

Two Varbinary fields' bitwise operation

Hi experts,
I'm badly looking for a solution which is related to the issue as below. Any help is highly apreciated.
I've created a table as
CREATE TABLE MyBinaryTable
(Field1 VARBINARY(100), Field2 VARBINARY(100), Result  VARBINARY(100))
Now what I need is I want to execute bitwise OR or AND operation between Field1 and Field2 and store in Result field. My Field values are like
Field1:
0x420C008418428C01C618638CB3DE7A0000000000630C2100000000000000
Field2:
0xE22D10DE5B6BADB5D65AEBBDB5D67A0000000000EF3D2100000000000000
I cannot convert these values to BIGINT because they are big enough.

Is there a way to execute bitwise operation between these?
0
shamal7
Asked:
shamal7
  • 8
  • 4
  • 2
1 Solution
 
twoboatsCommented:
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
0
 
Ken SelviaRetiredCommented:
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
0
 
shamal7Author Commented:
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.
0
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
Ken SelviaRetiredCommented:
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
0
 
Ken SelviaRetiredCommented:
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.

0
 
shamal7Author Commented:
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?
0
 
Ken SelviaRetiredCommented:
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


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

In fact it's a good thing.
0
 
shamal7Author Commented:
Ok kselvia, I'll try with your solution now, I think you should go to sleep :)
0
 
Ken SelviaRetiredCommented:
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

0
 
Ken SelviaRetiredCommented:
See another problem so here is the edited complete version


DECLARE @sql varchar(8000)
DEclare @hex1 varchar(120)
DEclare @hex2 varchar(120)


While 1 = 1
begin

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

If @hex1 IS NULL
   BREAK

SELECT @sql = COALESCE(@sql + ') + ',
'Update MyBinaryTable
set Result = Ored
FROM MyBinaryTable
JOIN
(
SELECT Field1='+@hex1+',
      Field2='+@hex2+',
      Ored = ') + 'convert (varbinary(2), Convert(int, 0x' +
      SubString( @hext, 2+ id*4 - 3, 4) + ' ) | ' +  
      'Convert(int, 0x' + SubString( @hex2, 2+ id*4 - 3, 4) + ' )
      ' FROM Numbers WHERE id*4 <= Len(  @hex1 )

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

exec (@sql )

end
0
 
Ken SelviaRetiredCommented:
Don't forget to create an index on Result or this will perform very badly
0
 
shamal7Author Commented:
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
0

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

  • 8
  • 4
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now