SQL Server T-SQL User-Defined 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 T-SQL 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 e-mail 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 T-SQL 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 T-SQL 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
+--------------------------------------------------------------+