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