SQL Clone
SQLServerCentral is supported by Redgate
Log in  ::  Register  ::  Not logged in

Get your favorite SSC scripts directly in SSMS with the free SQL Scripts addin. Search for scripts directly from SSMS, and instantly access any saved scripts in your SSC briefcase from the favorites tab.
Download now (direct download link)

Convert Interger to Binary String for Bit Masks

By rrinchuse,

The three functions here can be used to store Bit Masks as Integers.

These are functions to convert an integer into a binary string.
This can be useful if you store Bit Masks in your database as integers.

For example .. '00000000000011111001001001110011' to 1020531
Each Bit could represent an ON or OFF value. One integer column
could be equivalent to having 32 individual bit columns in your table.

There are three functions:

fn_GetBitString .. converts an integer to a bit string of a given size up to 32bit
fn_GetIntFromBitString .. converts a bit string to an integer
fn_GetBit .. get a specific bit value by bit location of an integer

Here are some example calls ..

DECLARE @I1 int, @I2 int

SELECT @I1 = dbo.fn_GetIntFromBitString('0101')
SELECT @I2 = dbo.fn_GetIntFromBitString('1001')

--Show Each Bit one at a time of the first integer
PRINT Cast(dbo.fn_GetBit(@I1,4) as char(1))
+Cast(dbo.fn_GetBit(@I1,3) as char(1))
+Cast(dbo.fn_GetBit(@I1,2) as char(1))
+Cast(dbo.fn_GetBit(@I1,1) as char(1))

--Do some Bitwise operations
PRINT dbo.fn_GetBitString( @I1 & @I2 ,4) --Bitwise AND
PRINT dbo.fn_GetBitString( @I1 | @I2 ,4) --Bitwise OR
PRINT dbo.fn_GetBitString( @I1 ^ @I2 ,4) --Bitwise XOR

Total article views: 716 | Views in the last 30 days: 1
Related Articles

Bitwise AND on CHAR datatype?

Is it possible to perform Bitwise AND, OR etc. on non-integer data?


SQL Bitwise Operations

How many DB professionals have never had to deal with bitwise operations in SQL Server?  Who has nev...


Bitwise Operations

Some time ago, I wrote an introductory post about bitwise operations in SQL Server.  I had fully int...


Using Bitwise Operators to Boost Performance

Bitwise operators can be challenging to manage at first. However, with practice and patience, and un...


Checking the bitwise value of an integer

Guys, My specific problem is that I need to check the Disabled status of an ActiveDomain account....