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
Using the power of SSIS and BCP to import MySQL Data and extract files from a BLOB(Varbinary) column.
2016-07-11
5,789 reads
A brief look at the Varbinary data type and its uses in SQL Server for beginners.
2013-02-07
11,330 reads
