Turn varbinary bit ON/OFF
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