Script of the day (1/4/2005) - Get the Value of a BitMask
-
I find it interesting that the the translation from bit map to decimal number is done left to right and not right to left. This not the convention I expected.
For example
'0' = 0
'1' = 1
'01' = 2
'11' = 3
'001' = 4
I guess I expected
'0' = 0
'1' = 1
'10' = 2
'11' = 3
'100' = 4
Is there a standard or convention for reading bit maps that I am unaware of?
Steve
-
SQL Server amazing often uses this reverse mirrored order. It's a bit annoying, but once you know that this is so, it's no big deal at all. I stumbled over this while trying to figure out file and page number in order to use DBCC PAGE. Since then I use a script by Itzik Ben-Gan to get these values:
USE PUBS
GO
DECLARE @page_adress AS BINARY(6)
SELECT
@page_adress = [first]
FROM
sysindexes
WHERE
[id] = OBJECT_ID('authors')
AND
indid=1
SELECT
CAST
(
SUBSTRING(@page_adress, 6, 1) +
SUBSTRING(@page_adress, 5, 1)
AS INT
 
AS file#, CAST
(
SUBSTRING(@page_adress, 4, 1) +
SUBSTRING(@page_adress, 3, 1) +
SUBSTRING(@page_adress, 2, 1) +
SUBSTRING(@page_adress, 1, 1) AS INT
 
AS page#DBCC TRACEON(3604)
DBCC PAGE('pubs',1,197,3)
DBCC TRACEOFF(3604)
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url] -
Btw, did someone here ever mentioned that this autoparsing for smilies is a BIT annoying.
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url] -
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
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply