Help with understanding a WHERE clause

Question

Post reply

Help with understanding a WHERE clause

Denesh Naidoo

SSCrazy

Points: 2348
More actions
August 23, 2010 at 7:29 am

#222981

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

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

Mark Cowne One Orange Chip Points: 26865 More actions · Answer 1

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/61537

Lowell SSC Guru Points: 323482 More actions · Answer 2

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

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Denesh Naidoo SSCrazy Points: 2348 More actions · Answer 3

Hi Mark and Lowell,

Thank you very much for your assistance. It is greatly appreciated.

Thanks

Denesh