Bit Manipulation Functions (4)

ofer bester, 2007-12-25 (first published: 2007-11-01)

Execute / Usage example script:

declare @BitMap BIGINT
declare @bitNo TINYINT

set @BitMap = 0
set @bitNo = 55

print '- Bit # = '+CONVERT(VARCHAR,@bitNo)+ ', BitMap value = '+ CONVERT(VARCHAR,@BitMap)
print ' Check bit #'+CONVERT(VARCHAR,@bitNo)
+' - Is bit ON = '+ CONVERT(VARCHAR,dbo.fnBitIsOn(@BitMap, @bitNo))
+', Value ='+ CONVERT(VARCHAR,@BitMap)

set @BitMap = dbo.fnBitSetOn(@BitMap, @bitNo)
print ' Turn bit #'+CONVERT(VARCHAR,@bitNo) +' ON'+
+' - Is bit ON = '+ CONVERT(VARCHAR,dbo.fnBitIsOn(@BitMap, @bitNo))
+', Value ='+ CONVERT(VARCHAR,@BitMap)

set @BitMap = dbo.fnBitSetOff(@BitMap, @bitNo)
print ' Turn bit #'+ CONVERT(VARCHAR,@bitNo) +' OFF'
+' - Is bit ON = '+ CONVERT(VARCHAR,dbo.fnBitIsOn(@BitMap, @bitNo))
+', Value ='+ CONVERT(VARCHAR,@BitMap)

set @BitMap = dbo.fnBitToggle(@BitMap, @bitNo)
print ' Toggle bit #'+ CONVERT(VARCHAR,@bitNo)
+' - Is bit ON = '+ CONVERT(VARCHAR,dbo.fnBitIsOn(@BitMap, @bitNo))
+', Value ='+ CONVERT(VARCHAR,@BitMap)
set @BitMap = dbo.fnBitToggle(@BitMap, @bitNo)
print ' Toggle bit #'+ CONVERT(VARCHAR,@bitNo)
+' - Is bit ON = '+ CONVERT(VARCHAR,dbo.fnBitIsOn(@BitMap, @bitNo))
+', Value ='+ CONVERT(VARCHAR,@BitMap)

print ''
set @BitMap = -1

print '- Bit # = '+CONVERT(VARCHAR,@bitNo)+ ', BitMap value = '+ CONVERT(VARCHAR,@BitMap)
set @BitMap = dbo.fnBitSetOn(@BitMap, @bitNo)
print ' Turn bit #'+CONVERT(VARCHAR,@bitNo) +' ON'+
+' - Is bit ON = '+ CONVERT(VARCHAR,dbo.fnBitIsOn(@BitMap, @bitNo))
+', Value ='+ CONVERT(VARCHAR,@BitMap)

Result:

- Bit # = 55, BitMap value = 0
Check bit #55 - Is bit ON = 0, Value =0
Turn bit #55 ON - Is bit ON = 1, Value =36028797018963968
Turn bit #55 OFF - Is bit ON = 0, Value =0
Toggle bit #55 - Is bit ON = 1, Value =36028797018963968
Toggle bit #55 - Is bit ON = 0, Value =0

- Bit # = 55, BitMap value = -1
Turn bit #55 ON - Is bit ON = 1, Value =-1
Turn bit #55 OFF - Is bit ON = 0, Value =-36028797018963969
Toggle bit #55 - Is bit ON = 1, Value =-1
Toggle bit #55 - Is bit ON = 0, Value =-36028797018963969

----------------------------------------------------------------------------------- -- Check if bigint Bit is ON ----------------------------------------------------------------------------------- CREATE FUNCTION dbo.fnBitIsOn (@BitMap BIGINT, @BitNo TINYINT = 0) RETURNS TINYINT -- v1.0.0 2006.09.26, Ofer Bester AS BEGIN IF (@BitNo > 63) RETURN 0 IF (@BitNo = 63) BEGIN IF (@BitMap >= 0) RETURN 0 ELSE RETURN 1 END IF ( @BitMap & POWER( CAST(2 AS BIGINT), @BitNo ) ) <> 0 RETURN 1 RETURN 0 END GO ----------------------------------------------------------------------------------- -- Set bigint Bit OFF ----------------------------------------------------------------------------------- CREATE FUNCTION dbo.fnBitSetOff (@BitMap BIGINT, @BitNo TINYINT = 0) RETURNS BIGINT -- v1.0.0 2006.09.26, Ofer Bester AS BEGIN IF (@BitNo > 63) RETURN @BitMap IF (@BitNo = 63) RETURN @BitMap & 0x7FFFFFFFFFFFFFFF RETURN (@BitMap & ~POWER( CAST(2 AS BIGINT), @BitNo )) -- Set bit # @BitNo OFF END GO ----------------------------------------------------------------------------------- -- Set bigint Bit ON ----------------------------------------------------------------------------------- CREATE FUNCTION dbo.fnBitSetOn (@BitMap BIGINT, @BitNo TINYINT = 0) RETURNS BIGINT -- v1.0.0 2006.09.26, Ofer Bester AS BEGIN IF (@BitNo > 63) RETURN @BitMap IF (@BitNo = 63) RETURN @BitMap | 0x8000000000000000 RETURN (@BitMap | POWER( CAST(2 AS BIGINT), @BitNo )) -- Set bit # @BitNo ON END GO ----------------------------------------------------------------------------------- -- Toggle bigint Bit ON/OFF ----------------------------------------------------------------------------------- CREATE FUNCTION dbo.fnBitToggle (@BitMap BIGINT, @BitNo TINYINT = 0) RETURNS BIGINT -- v1.0.0 2006.09.26, Ofer Bester AS BEGIN IF ( dbo.fnBitIsOn(@BitMap, @BitNo) = 1 ) RETURN dbo.fnBitSetOff(@BitMap, @BitNo) RETURN dbo.fnBitSetOn(@BitMap, @BitNo) END

A Normalization Primer

by Brian Kelley

SQLServerCentral.com

T-SQL

For most DBAs, normalization is an understood concept, a bread and butter bit of knowledge. However, it is not at all unusual to review a database design by a development group for an OLTP (OnLine Transaction Processing) environment and find that the schema chosen is anything but properly normalized. This article by Brian Kelley will give you the core knowledge to data model.

★ ★ ★ ★ ★ ★ ★ ★ ★ ★

5 (3)

You rated this post out of 5. Change rating

2003-01-13

18,496 reads

Discuss

Reaching the Outer Limits

by Steve Jones

SQLServerCentral.com

T-SQL

What's this error:Arithmetic overflow error converting IDENTITY to data type int? It was a new one to me, but read on to find out what it means.

★ ★ ★ ★ ★ ★ ★ ★ ★ ★

5 (2)

You rated this post out of 5. Change rating

2002-10-14

6,946 reads

Discuss

Working with email addresses in SQL Server!

by Additional Articles

Other

T-SQL

This article shows you how to design the storage for email addresses, how to validate email addresses, how to retrieve demographic information from email addresses efficiently, using computed columns and indexes. It also covers the security aspect of dealing with email addresses.

2002-08-01

1,340 reads