Solved

Two Varbinary fields' bitwise operation

Posted on 2007-11-14
14
1,186 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
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
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 250 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

What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

Question has a verified solution.

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

Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function

729 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