Celebrate National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Two Varbinary fields' bitwise operation

Posted on 2007-11-14
14
Medium Priority
?
1,188 Views
Last Modified: 2008-02-01
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
Comment
Question by:shamal7
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 8
  • 4
  • 2
14 Comments
 
LVL 14

Expert Comment

by:twoboats
ID: 20278555
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
 
LVL 12

Expert Comment

by:kselvia
ID: 20278588
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
 

Author Comment

by:shamal7
ID: 20278599
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
Ransomware: The New Cyber Threat & How to Stop It

This infographic explains ransomware, type of malware that blocks access to your files or your systems and holds them hostage until a ransom is paid. It also examines the different types of ransomware and explains what you can do to thwart this sinister online threat.  

 
LVL 12

Expert Comment

by:kselvia
ID: 20278603
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
 
LVL 12

Expert Comment

by:kselvia
ID: 20278630
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
 

Author Comment

by:shamal7
ID: 20278658
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
 
LVL 12

Expert Comment

by:kselvia
ID: 20278664
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
 
LVL 12

Expert Comment

by:kselvia
ID: 20278670
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
 

Author Comment

by:shamal7
ID: 20278703
Ok kselvia, I'll try with your solution now, I think you should go to sleep :)
0
 
LVL 12

Expert Comment

by:kselvia
ID: 20278717
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
 
LVL 12

Accepted Solution

by:
kselvia earned 750 total points
ID: 20278731
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
 
LVL 12

Expert Comment

by:kselvia
ID: 20278732
Don't forget to create an index on Result or this will perform very badly
0
 

Author Comment

by:shamal7
ID: 20287318
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

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.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
In part one, we reviewed the prerequisites required for installing SQL Server vNext. In this part we will explore how to install Microsoft's SQL Server on Ubuntu 16.04.
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
Via a live example, show how to shrink a transaction log file down to a reasonable size.

730 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question