This is as set based solution for the same probleme.
I've tested it's speed and it goes 2 to 10 times faster than the original script depending on the length of the string to parse.
However for optimium speed the validation should be removed (when possible) since it eats up about 80% of the work in the function.
CREATE TABLE [Numbers] (
[PkNumber] [int] IDENTITY (1, 1) NOT NULL ,
CONSTRAINT [Pk_Number] PRIMARY KEY CLUSTERED
(
[PkNumber]
) ON [PRIMARY]
) ON [PRIMARY]
GO
Declare @i as int
set @i = 0
--I use this table for string operations as well, but in this case we could stop at 64.
while @i < 8000
begin
Insert into dbo.Numbers Default values
set @i = @i + 1
end
GO
CREATE FUNCTION [dbo].[fnBitMaskToBigInt] (@BitMask as varchar(63), @IsBinaryFormated as bit = 1)
RETURNS BIGINT AS
BEGIN
/*
@IsBinaryFormated = 1 means will make the function parse from right to left like for a binary number
@IsBinaryFormated = 0 will read from left to right
*/
Declare @Return as bigint
if Replace(Replace(@BitMask, '1', ''), '0', '') = '' and Charindex(' ', @BitMask, 1) = 0
set @Return =
(Select sum(dtBitValues.Powers) as BitMaskValue from
(Select case substring(dtBitMask.BitMask, PkNumber, 1)
when 1 then power(cast(2 as bigint), PkNumber - 1) else 0 end as Powers
from dbo.Numbers cross join (Select case @IsBinaryFormated when 1 then reverse(@BitMask) else @BitMask end as BitMask) dtBitMask
where PkNumber <= len(dtBitMask.BitMask)) dtBitValues)
else
set @Return = -1
Return @Return
END
GO
--usage
Select dbo.fnBitMaskToBigInt ('0A1', 0) -- = -1 (invalid bitmask)
Select dbo.fnBitMaskToBigInt (' 10 10', 0) -- = -1 (invalid bitmask)
Select dbo.fnBitMaskToBigInt ('0101', 0) -- = 10 (read left to right)
Select dbo.fnBitMaskToBigInt ('0101', 1) -- = 5 (read right to left or as a binary number)
Select dbo.fnBitMaskToBigInt ('111111111111111111111111111111111111111111111111111111111111111', 1) -- = 9223372036854775807
--DROP FUNCTION fnBitMaskToBigInt
GO
--DROP TABLE Numbers
GO