Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
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: 700 | Views in the last 30 days: 3
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...


Convert integers/numbers into binary format - viceversa

This functions are used to convert integers/numbers into binary format and viceversa.


Join the most active online SQL Server Community

SQL knowledge, delivered daily, free:

Email address:  

You make SSC a better place

As a member of SQLServerCentral, you get free access to loads of fresh content: thousands of articles and SQL scripts, a library of free eBooks, a weekly database news roundup, a great Q & A platform… And it’s our huge, buzzing community of SQL Server Professionals that makes it such a success.

Join us!

Steve Jones

Already a member? Jump in:

Email address:   Password:   Remember me: Forgotten your password?
Steve Jones