
SQL Server TSQL UserDefined Function of the Week
UDF to Lets you Pick A Bit from an Int
Volume 1 Number 24 April 29, 2003
++
Hello from Novick Software www.NovickSoftware.com
++
Visit http://www.NovickSoftware.com for additional material
about SQL Server, Visual Basic, ASP, XML, and security.
Visit the TSQL UDF of the Week at:
http://www.NovickSoftware.com/UdfOfWeek/UDFofWeek.htm
Check out the UDF Frequently Asked Questions at:
http://www.novicksoftware.com/UdfOfWeek/UDF_FAQ.htm
To ask a UDF related question or to contribute a UDF,
send email to udf@NovickSoftware.com
Sign up for this newsletter at:
http://www.novicksoftware.com/UdfOfWeek/UDFofWeekSignup.htm
++
Last week we took a look at an undocumented system UDF,
fn_replinttobitstring, that converts an int into a 32 character
string representing each of the 32 possible bits in the int.
Ints are sometimes used to store a group of individual bits
in a compact way. It can be rather difficult to examine the
one bit that you're interested in and that's the subject of
this issue's UDF.
udf_Bit_Int_NthBIT is a scalar UDF that returns the value of the
Nth bit of an int. For the purpose of this function and most
other functions that work with individual bits of any integer
type, bits are numbered with 0 being the least significant bit
of the int up to 31 which is the sign bit.
fn_replinttobitstring represent bits as binary numbers so the
least significant bit (number 0) appears on the right of the
string and the sign bit (number 31) appears on the left. So
00000000000000000000000000000011
equals three.
Here's the CREATE FUNCTION script for our UDF:
/ Start Copying from below this line \
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
CREATE FUNCTION dbo.udf_Bit_Int_NthBIT (
@BitField int
, @Nth int  0th bit on right
) RETURNS bit  The @Nth bit in @BitField
WITH SCHEMABINDING
/*
* Returns the Nth bit in a int. Bits are numbered with
* zero begin the least significant bit up to 31 which is the
* sign bit of the int. if @Nth is > 31 it returns NULL.
*
* Example:
SELECT dbo.udf_Bit_Int_NthBIT (3, 0)
*
* © Copyright 2003 Andrew Novick http://www.NovickSoftware.com
* You may use this function in any of your SQL Server databases
* including databases that you sell, so long as they contain
* other unrelated database objects. You may not publish this
* UDF either in print or electronically.
* Published in the TSQL UDF of the Week Vol 1 #
http://www.NovickSoftware.com/UDFofWeek/UDFofWeek.htm
****************************************************************/
AS BEGIN
RETURN
CASE WHEN @NTH BETWEEN 0 and 30 THEN
CASE WHEN ABS(SIGN(@BitField & (POWER(2,@Nth))))=1
THEN 1
ELSE 0
END
WHEN @Nth = 31 THEN
CASE WHEN @BitField < 0 THEN 1 ELSE 0 END
ELSE NULL
END
END
GO
GRANT EXEC, REFERENCES on dbo.udf_Bit_Int_NthBIT to PUBLIC
GO
\ Stop Copying above this line /
The Query below uses udf_Bit_Int_NthBIT and last week's function
udf_Bits_FromInt to illustrate how udf_Bit_Int_NthBIT works.
/ Start Copying from below this line \
SELECT dbo.udf_BitS_FromInt (37, 0) as
[33222222222211111111110000000000
10987654321098765432109876543210]
SELECT
dbo.udf_Bit_Int_NthBIT (37, 32) as [32]
, dbo.udf_Bit_Int_NthBIT (37, 31) as [31]
, dbo.udf_Bit_Int_NthBIT (37, 30) as [30]
, dbo.udf_Bit_Int_NthBIT (37, 29) as [29]
, dbo.udf_Bit_Int_NthBIT (37, 10) as [10]
, dbo.udf_Bit_Int_NthBIT (37, 9) as [9]
, dbo.udf_Bit_Int_NthBIT (37, 8) as [8]
, dbo.udf_Bit_Int_NthBIT (37, 7) as [7]
, dbo.udf_Bit_Int_NthBIT (37, 6) as [6]
, dbo.udf_Bit_Int_NthBIT (37, 5) as [5]
, dbo.udf_Bit_Int_NthBIT (37, 4) as [4]
, dbo.udf_Bit_Int_NthBIT (37, 3) as [3]
, dbo.udf_Bit_Int_NthBIT (37, 2) as [2]
, dbo.udf_Bit_Int_NthBIT (37, 1) as [1]
, dbo.udf_Bit_Int_NthBIT (37, 0) as [0]
GO
\ Stop Copying above this line /
(Results)
33222222222211111111110000000000
10987654321098765432109876543210

11111111111111111111111111011011
32 31 30 29 10 9 8 7 6 5 4 3 2 1 0
              
NULL 1 1 1 1 1 1 1 1 0 1 1 0 1 1
The positioning of the column alias in the first SELECT statement
is deliberate. I want to produce the two line header above the
output of udf_BitS_FromInt.
Next week udf_Bit_Int_Nth will come in handy as we use it to
examine the status column in sysobjects. sysobjects.status
is undocumented, but its third bit has the information that
we'll need.
++
Do you have a great UDF that you'd like to share? Or maybe you
have a TSQL problem that you think could be solved by a UDF
but you don't know how? Send them to:
UDF@NovickSoftware.com
and they might be published in this newsletter. I try and
respond to every request that I get.
Thanks,
Andrew Novick
++

