August 23, 2010 at 7:29 am
Hi All,
Working on SQL Server 2005.
Have a query that has a WHERE clause as follows:
WHERE (Documents.Flags & 0x18)=0
Can someone please explain the & 0x18 part which I have no idea on. The field Flags is a BigInt field
Thanks in advance
Denesh
August 23, 2010 at 7:40 am
That is a bitwise "AND" and will return rows where both bits 4 and 5 are zero
http://msdn.microsoft.com/en-US/library/ms174965(v=SQL.100).aspx
____________________________________________________
Deja View - The strange feeling that somewhere, sometime you've optimised this query before
How to get the best help on a forum
http://www.sqlservercentral.com/articles/Best+Practices/61537August 23, 2010 at 7:48 am
Denesh Naidoo (8/23/2010)
Hi All,Working on SQL Server 2005.
Have a query that has a WHERE clause as follows:
WHERE (Documents.Flags & 0x18)=0
Can someone please explain the & 0x18 part which I have no idea on. The field Flags is a BigInt field
Thanks in advance
Denesh
someone is using the bigint field Documents.Flags as multiple yes/no flags.
a bigint, when mapped to a biniary flags, might look like this:
--0x18 as 32 bit binary map...big int of 32 more zeros not needed.
0000000000000000000000000011000
you'll have to get witht he developers to come up with what each of the 0/1 yes/no bit flags mean...this is one of the "non-no"s of a DBMS system, storing more than one value in a single field, but it's actually used a lot.
here's a way to map any single value to a 0/1 to a visual map like i demo-ed above;
DECLARE @IntVal int;
SET @IntVal = 0x18; --the value to map
WITH CTE (IntVal, BinVal, FinalBin) AS
(SELECT @IntVal IntVal, @IntVal % 2 BinVal, CONVERT(varchar(MAX),@IntVal % 2) FinalBin
UNION ALL
SELECT IntVal / 2, (IntVal / 2) % 2, CONVERT(varchar(MAX),(IntVal / 2) % 2) + FinalBin FinalBin
FROM CTE
WHERE IntVal / 2 > 0)
SELECT right('0000000000000000000000000000000' + FinalBin ,31)
FROM CTE
WHERE IntVal =
(SELECT MIN(IntVal)
FROM CTE);
Lowell
August 24, 2010 at 12:14 am
Hi Mark and Lowell,
Thank you very much for your assistance. It is greatly appreciated.
Thanks
Denesh
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply