Comparing multiple bit fields - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Comparing multiple bit fields

Author

Topic

R
Constraint Violating Yak Guru

328 Posts

Posted - 2008-11-18 : 09:26:49

I have a temporary table of results, containing an ID (int) column and a Bit column. For example,

ID BITFIELD
-------------
1 1
2 1
3 0
4 1
5 1
6 1

I need to loop through all rows and return a variable that shows whether any 0 bit values are found. For example, if all rows contain 1 in the bit field, then the variable should be set to 1. If any 0 values are found, then the variable should be set to 0.

So in the above example, the variable would be set to 0 because row 3 has a 0 bit value.

Is there a quick way to do this in SQL 2005 please?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-11-18 : 09:28:46

[code]SELECT CASE WHEN SUM(CASE WHEN BITFIELD = 0 THEN 1 ELSE 0 END) >0 THEN 0 ELSE 1 END FROM Table[/code]

bjoerns
Posting Yak Master

154 Posts

Posted - 2008-11-18 : 09:41:27

[code]SELECT MIN(BITFIELD) FROM Table[/code]

R
Constraint Violating Yak Guru

328 Posts

Posted - 2008-11-18 : 09:57:30

Hi

Thanks for your replies.

@visakh16
This code works really well.

@bjoerns
I get an error here: Operand data type bit is invalid for min operator.

bjoerns
Posting Yak Master

154 Posts

Posted - 2008-11-18 : 10:01:53

Sorry, should have tried it out.

SELECT MIN(CAST(BITFIELD AS int)) FROM Table

R
Constraint Violating Yak Guru

328 Posts

Posted - 2008-11-18 : 10:15:00

Thanks :-)

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-11-20 : 02:52:07

quote:
Originally posted by bjoerns

Sorry, should have tried it out.

SELECT MIN(CAST(BITFIELD AS int)) FROM Table

SELECT MIN(BITFIELD*1) FROM Table

Madhivanan

Failing to plan is Planning to fail

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-11-20 : 03:15:20

Can I play too?

DECLARE	@Sample TABLE
	(
		bitField BIT
	)

INSERT	@Sample
SELECT	1 UNION ALL
SELECT	1 UNION ALL
SELECT	0 UNION ALL
SELECT	1 UNION ALL
SELECT	1 UNION ALL
SELECT	1

-- Visakh16
SELECT	CASE
		WHEN SUM(CASE WHEN BITFIELD = 0 THEN 1 ELSE 0 END) > 0 THEN 0
		ELSE 1
	END
FROM	@Sample

-- bjoerns
SELECT	MIN(CAST(bitField AS INT))
FROM	@Sample

-- Madhivanan
SELECT	MIN(bitField * 1)
FROM	@Sample

-- Peso
SELECT	MIN(SIGN(bitField))
FROM	@Sample

E 12°55'05.63"
N 56°04'39.26"

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-11-20 : 04:03:14

SELECT MIN(ABS(bitField))
FROM @Sample

Madhivanan

Failing to plan is Planning to fail

R
Constraint Violating Yak Guru

328 Posts

Posted - 2008-11-20 : 04:09:40

LOL you guys are showing off!

Now I really am spoilt for choice...

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources