SQL Server T-SQL User-Defined Function of the Week

fn_replinttobitstring an Undocumented System UDF for Working With Bit Fields

Volume 1 Number 23     April 22, 2003


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

If you missed it you might still want to go back and take a look
at my new article about fn_get_sql on Database Journal.  You'll
find the article at:
http://www.databasejournal.com/features/mssql/article.php/2189761
The video that accompanies it is on my site at:
http://www.novicksoftware.com/VideoAbout_fn_get_sql.htm

Last week's issue, #22, introduced you to the undocumented
system UDFs.  This issue continues with another undocumented
system UDF and delves into the bits that make up an int.

fn_replinttobitstring is an undocumented system UDF that converts
an integer into a 32 character long string of 1s and 0s that 
represent the bit pattern of the integer.  The format of the 
function call is:

        fn_replinttobitstring (@INT )
        
@INT is any int or an expression that can be converted to an 
int.   @INT is typically used to hold a field of bits, packing
up to 32 separate values into one int.

Packing many bits into an int runs counter to relational ideals
and rules of normalization.  It's common in embedded systems,
binary protocols such as TCP/IP, and older systems that were
very short on disk space and memory.  It's also used in several
system tables.

The return value from fn_replinttobitstring is a char(32) string
of ones and zeros.  Each character represents one bit of the 
int.  The least significant position is the 32nd character, the
one on the right.  The return value is intended for reading by
a human like you or me.

Lets look at a couple of examples:
/-------- Start copying from below this line -------------------\
-- Sample queries for fn_replinttobitstring
select fn_replinttobitstring (26) as [26 in binary]
     , fn_replinttobitstring (-1) as [-1]
go
select fn_replinttobitstring (0x8FFFFFFF) as [Most positive num]
     , fn_replinttobitstring (0x80000000) as [Most negative num]
go
\-------- Stop copying above this line   -----------------------/
(Results)
26 in binary                     -1                          
-------------------------------- --------------------------------
00000000000000000000000000011010 11111111111111111111111111111111

Most positive num                Most negative num            
-------------------------------- --------------------------------
01111111111111111111111111111111 10000000000000000000000000000000

The numbers in the second query above that start with 0x are 
T-SQL's  "binary" constants.  I've put the word binary in quotes
because T-SQL's binary constants are actually hexadecimal
constants.

The first bit in an int is the sign bit.  The other 31 bits are
used for data. We can see this in the 2nd result set.

udf_Bits_FromINT is a UDF that builds on fn_replinttobitstring.
In addition to taking a int argument, its second parameter is a
BIT argument that requests that leading zeros be eliminated.  For
bit fields that only contain a few bits, dropping the leading 0s
is more readable.  Here's the create function script:

/-------- Start copying from below this line -------------------\
SET QUOTED_IDENTIFIER ON 
SET ANSI_NULLS ON 
GO

CREATE  FUNCTION dbo.udf_BitS_FromInt (

    @INT int -- the input value
  , @TrimLeadingZerosBIT bit = 0 --  1 to trim leading 0s.
)   RETURNS varchar(32) -- String of 1s and 0s representing @INT
    -- No schemabinding due to use of system UDF.
/*
* Translates an int into the a corresponding 32 character string
* of 1s and 0s. It will optionally trim leading zeros.
*
* Related Functions: fn_replinttobitstring used in this UDF.
* Common Usage:
select dbo.udf_BITS_FromInt(26, 0) as [With leading 0s]
     , dbo.udf_BITS_FromInt(26, 1) as [Sans leading 0s]
* Test:
PRINT 'Test 1    ' + CASE WHEN '11010' = 
  dbo.udf_BITS_FromInt (26, 1) THEN 'Worked' ELSE 'ERROR' END
*
* © 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 #23  4/22/03
http://www.NovickSoftware.com/UDFofWeek/UDFofWeek.htm
****************************************************************/
AS BEGIN

    DECLARE @WorkingVariable varchar(32)
          , @PosOfFirst1 int

    SELECT @WorkingVariable = fn_replinttobitstring (@INT)

    IF @TrimLeadingZerosBIT=1 BEGIN

       SET @PosOfFirst1 = CHARINDEX( '1', @WorkingVariable, 1)
    
       SET @WorkingVariable =
            CASE @PosOfFirst1
                WHEN 1 THEN @WorkingVariable -- Negative Number
                WHEN 0 THEN '0' -- return at least 1 of the 0s
                ELSE SUBSTRING (@WorkingVariable
                              , @PosOfFirst1
                              , 32 - @PosOfFirst1 + 1)
                END
    END -- IF 

    Return @WorkingVariable
END
GO

GRANT  REFERENCES ,  EXECUTE  ON [dbo].[udf_Bits_FromInt]  
        TO [PUBLIC]
GO
\-------- Stop copying above this line   -----------------------/

Now take a look at the differences between fn_replinttobitstring
and udf_Bits_FromInt:

/-------- Start copying from below this line -------------------\
-- Show difference of fn_replinttobitstring and udf_BitS_FromInt
SELECT fn_replinttobitstring (37) 
                       as [37 from fn_replinttobitstring]
     , dbo.udf_BitS_FromInt (37, 1) 
                       as [37 from udf_BitS_FromInt]
GO
\-------- Stop copying above this line   -----------------------/
(Results)
37 from fn_replinttobitstring    37 from udf_BitS_FromInt      
-------------------------------- --------------------------------
00000000000000000000000000100101 100101

A few of the fields in system tables that are bit fields and
that might be interesting to display with udf_BitS_FromInt are:

sysfiles.status
syscomments.texttype
sysconfigures.status
sysconstraints.status
syscurconfigs.status

There is also a function, fn_replbitstringtoint, that is the 
complement of fn_replinttobitstring.  We'll take a look at it in
another issue.

Please share this issue with anyone interested in SQL Server.
Thanks.

+--------------------------------------------------------------+
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
+--------------------------------------------------------------+