Solved

# Two Varbinary fields' bitwise operation

Posted on 2007-11-14
1,180 Views
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:
I cannot convert these values to BIGINT because they are big enough.

Is there a way to execute bitwise operation between these?
0
Question by:shamal7
• 8
• 4
• 2

LVL 14

Expert Comment

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

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

Author Comment

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

LVL 12

Expert Comment

Correction, it generates and executes this

Update MyBinaryTable
set Result = Ored
FROM MyBinaryTable
JOIN
(
SELECT Field1=0x420c008418428c01c618638cb3de7a0000000000630c2100000000000000,
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

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

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

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

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

LVL 14

Expert Comment

0

Author Comment

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

LVL 12

Expert Comment

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

kselvia earned 250 total points
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

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

Author Comment

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

In this article I will describe the Copy Database Wizard method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…