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

Convert Bit Strings Back to Integers

Volume 1 Number 26      May 27, 2003

Sign up for this newsletter at:
http://www.novicksoftware.com/UdfOfWeek/UDFofWeekSignup.htm

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

Various functions and system tables use bit fields as a very 
compact way to store several bits in one int.  A few weeks ago 
Issue #23 discussed the undocumented system UDF
fn_replinttobitstring, which converts an int to a 32 character 
string of ones and zeros. This is usually done for human
consumption.  That newsletter also included udf_BitS_FromInt,
which does the same job as fn_replinttobitstring but can be 
more convenient because it strips leading zeros.

fn_replinttobitstring has a complementary function 
fn_replbitstringtoint, which is also an undocumented system UDF.
It's a scalar function with he syntax:

         fn_replbitstringtoint (@Bitstring)
         
@Bitstring is a 32-character string of 1s and 0s that represent
the bits of an integer.  For example:
'00000000000000000000000000000010' represents 2. 

This query shows some examples of fn_replbitstringtoint:
/------- Start copying below this line -------------------------\
-- examples of fn_replbitstringtoint
select fn_replbitstringtoint ('00000000000000000000000000000010') as [2]
     , fn_replbitstringtoint ('11111111111111111111111111111111') as [-1]
     , fn_replbitstringtoint ('10000000000000000000000000000000') 
                                        as [Most negative number]
     , fn_replbitstringtoint ('01111111111111111111111111111111') 
                                         as [Most positive number]
go
\-------Stop copying above this line ---------------------------/
(Results)
2           -1          Most negative number Most positive number 
----------- ----------- -------------------- ------------------- 
2           -1          -2147483648          2147483647

fn_replbitstringtoint doesn't handle missing leading zeros well.
In fact, it assumes that they're missing trailing zeros.  That's
not a very useful interpretation as shown in this query:
/------- Start copying below this line -------------------------\
-- what happens when there are missing leading zeros
select fn_replbitstringtoint ('00000000000000000000000000001100') as [12]
     , fn_replbitstringtoint ('1100') as [12 without leading zeros]
go 
\-------Stop copying above this line ---------------------------/
12          12 without leading zeros 
----------- ------------------------ 
12          -1073741824

The function udf_BitS_ToInt improves on fn_replbitstringtoint by 
padding with leading zeros before using fn_replbitstringtoint
to do the actual conversion.  Here's the CREATE FUNCTION script:
/------- Start copying below this line -------------------------\
SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO

CREATE FUNCTION dbo.udf_BitS_ToInt (

    @BitString varchar(32) -- Bit pattern for the input
)   RETURNS int
    -- No SCHEMABINDING, uses system UDF
/*
* Converts a bit string of up to 32 characters into the 
* corresponding int.  The string is padded on the left with any
* missing zeros.
*
* Related Functions: the undocumented built-in function 
*         fn_replbitstringtoint is similar but does not handle
*         missing leading zeros the same way.
* Maintenance Notes: based on undocumented built-in function 
*          fn_replbitstringtoint, will have to change if it's 
*          behavior changes.
*
* Example:
SELECT dbo.udf_BitS_ToInt('1101') -- should return 13
*
* Test:
PRINT 'Test 1   13       ' + CASE WHEN 13 = 
  dbo.udf_BitS_ToInt ('1101') THEN 'Worked' ELSE 'ERROR' END
PRINT 'Test 2   -1       ' + CASE WHEN -1=dbo.udf_BitS_ToInt 
                        ('11111111111111111111111111111111')
                         THEN 'Worked' ELSE 'ERROR' END
PRINT 'Test 2 biggest int ' + CASE WHEN 2147483647=
  dbo.udf_BitS_ToInt ('01111111111111111111111111111111')
                     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 #26  May 13, 2003
http://www.NovickSofware.com/UDFofWeek/UDFofWeek.htm
****************************************************************/
AS BEGIN

    DECLARE @WorkingVariable int

    -- fn_replbitstringtoint is used but the argument is prepped 
    -- by padding with the correct number of leading zeros.
    SELECT @WorkingVariable = fn_replbitstringtoint (
                RIGHT('000000000000000000000000000000000000' 
                + @Bit String, 32))

    Return @WorkingVariable
END

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

Lets look at a few calls to udf_BitS_ToInt and a comparison with
fn_replbitstringtoint:
/------- Start copying below this line -------------------------\
-- try out udf_BitS_ToInt and compare with fn_replbitstringtoint
SELECT 
  dbo.udf_BitS_ToInt ('11111111111111111111111111111111') as [-1]
     , dbo.udf_BitS_ToInt ('01111111111111111111111111111111') 
                                         as [Most positive num]
, dbo.udf_BitS_ToInt ('00000000000000000000000000011010') as [26]
     , dbo.udf_BitS_ToInt ('11010') as [26 sans leading 0s]
     , fn_replbitstringtoint ('11010') as [fn_replbit...]  
go
\-------Stop copying above this line ---------------------------/
(Results)
-1     Most positive num   26    26 sans leading 0s fn_replbit... 
------ ------------------- ----- ------------------ ------------- 
    -1          2147483647    26                 26    -805306368 

Using udf_BitS_FromInt and udf_BitS_ToInt together make it pretty
easy to use and understand ints as bit fields.


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