Turn varbinary bit ON/OFF

ofer bester, 2012-05-22 (first published: 2012-05-01)

The script create two functions one for turning specific bit ON theother for turning it OFF. Also included 3 test / usage samples scripts.

Each functions accept varbinary(max) and bit index as input parameters and return varbinary with requested bit turned ON / OFF.

set nocount on GO -------------------------------------------------------------------------------- -- Turn varbinary bit ON -------------------------------------------------------------------------------- drop FUNCTION [dbo].[fnTurnBitOn] GO create FUNCTION [dbo].[fnTurnBitOn] ( @Buffer VARBINARY(MAX) ,@BitIndex BIGINT -- Left-to-right zero based index. ) RETURNS VARBINARY(MAX) AS BEGIN DECLARE @ByteIndex BIGINT = @BitIndex / 8 IF (@ByteIndex < LEN(@Buffer)) BEGIN DECLARE @Byte VARBINARY(1) = SUBSTRING(@Buffer, @ByteIndex +1, 1) DECLARE @Bit TINYINT = 7 -(@BitIndex - @ByteIndex *8) SET @Byte = @Byte | POWER( 2, @Bit ) -- Set bit # @BitNo ON SET @Buffer = CAST(STUFF(@Buffer , @ByteIndex+1 , 1 ,@Byte )AS VARBINARY(MAX)) END RETURN @Buffer END GO -------------------------------------------------------------------------------- -- Turn varbinary bit OFF -------------------------------------------------------------------------------- drop FUNCTION [dbo].[fnTurnBitOff] GO create FUNCTION [dbo].[fnTurnBitOff] ( @Buffer VARBINARY(MAX) ,@BitIndex BIGINT -- Left-to-right zero based index. ) RETURNS VARBINARY(MAX) AS BEGIN DECLARE @ByteIndex BIGINT = @BitIndex / 8 IF (@ByteIndex < LEN(@Buffer)) BEGIN DECLARE @Byte VARBINARY(1) = SUBSTRING(@Buffer, @ByteIndex +1, 1) DECLARE @Bit TINYINT = 7 -(@BitIndex - @ByteIndex *8) SET @Byte = @Byte &~ POWER( 2, @Bit ) -- Set bit # @BitNo OFF SET @Buffer = CAST(STUFF(@Buffer , @ByteIndex+1 , 1 ,@Byte )AS VARBINARY(MAX)) END RETURN @Buffer END GO -------------------------------------------------------------------------------- -- TESTs -------------------------------------------------------------------------------- declare @buffer varbinary(MAX) = 0x0000000000000000000000 declare @i int = 0 while @i <= LEN(@buffer) * 8 begin set @buffer = [dbo].[fnTurnBitOn](@buffer, @i) select 'Set Bit #'+ convert(varchar, @i) +' ON : ', @buffer set @buffer = [dbo].[fnTurnBitOff](@buffer, @i) select 'Set Bit #'+ convert(varchar, @i) +' OFF: ', @buffer set @i = @i +1 end go -- -- Turn 1350 bytes varbinary bits ON than OFF -- declare @buffer varbinary(MAX) = 0x000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000 print 'Buffer size: '+ convert(varchar,len(@buffer)) + ' bytes.' declare @i int = 0 while @i <= LEN(@buffer) * 8 begin set @buffer = [dbo].[fnTurnBitOn](@buffer, @i) print @buffer set @i = @i +1 end set @i = 0 while @i <= LEN(@buffer) * 8 begin set @buffer = [dbo].[fnTurnBitOff](@buffer, @i) print @buffer set @i = @i +1 end