IF OBJECTPROPERTY( OBJECT_ID( 'dbo.fn_to_bitstr' ) , 'IsScalarFunction' ) IS NOT NULL
    DROP FUNCTION dbo.fn_to_bitstr
GO
CREATE FUNCTION fn_to_bitstr (
    @IntegerVal sql_variant
)
RETURNS varchar( 255 )
AS
BEGIN
    /********************************************************************************/
    /* Created By       : Umachandar Jayachandran (UC)                                  */
    /* Created On       : 24 December 2000                                              */
    /* Description      : This function returns a string of bits for the specified      */
    /*                integer or binary values.                                     */
    /********************************************************************************/
    /*  Resources  :    https://umachandar.com/resources.htm                         */
    /********************************************************************************/
    DECLARE @BitStr varchar( 255 ) , @Bytes int , @BinaryVal varbinary( 64 )
    IF SQL_VARIANT_PROPERTY( @IntegerVal , 'BaseType' ) NOT IN (
            'tinyint' , 'smallint' , 'int' , 'bigint', 'binary', 'varbinary' )
            RETURN NULL
    SET @BinaryVal = CAST( @IntegerVal AS varbinary(8000) )
    SET @Bytes     = LEN( @BinaryVal )
    SET @BitStr    = ''
    WHILE ( @Bytes > 0 )
        SELECT @BitStr = CAST( SIGN( SUBSTRING( @BinaryVal , @Bytes , 1 ) &
                                     POWER( 2 , 7 ) ) AS varchar ) +
                         CAST( SIGN( SUBSTRING( @BinaryVal , @Bytes , 1 ) &
                                     POWER( 2 , 6 ) ) AS varchar ) +
                         CAST( SIGN( SUBSTRING( @BinaryVal , @Bytes , 1 ) &
                                     POWER( 2 , 5 ) ) AS varchar ) +
                         CAST( SIGN( SUBSTRING( @BinaryVal , @Bytes , 1 ) &
                                     POWER( 2 , 4 ) ) AS varchar ) +
                         CAST( SIGN( SUBSTRING( @BinaryVal , @Bytes , 1 ) &
                                     POWER( 2 , 3 ) ) AS varchar ) +
                         CAST( SIGN( SUBSTRING( @BinaryVal , @Bytes , 1 ) &
                                     POWER( 2 , 2 ) ) AS varchar ) +
                         CAST( SIGN( SUBSTRING( @BinaryVal , @Bytes , 1 ) &
                                     POWER( 2 , 1 ) ) AS varchar ) +
                         CAST( SIGN( SUBSTRING( @BinaryVal , @Bytes , 1 ) &
                                     POWER( 2 , 0 ) ) AS varchar ) +
                         @BitStr ,
               @Bytes = @Bytes - 1
   RETURN( @BitStr )
END
Go

PRINT dbo.fn_to_bitstr( cast( 1212312321452352 as bigint ) )
/*
0000000000000100010011101001011101110101010001101110100101000000
*/
PRINT dbo.fn_to_bitstr( 912817241 )
/*
00110110011010000111110001011001
*/
PRINT dbo.fn_to_bitstr( cast( 7 as money ) )
/*
(null)
*/
PRINT dbo.fn_to_bitstr( 0xAf )
/*
10101111
*/

This page was last updated on May 01, 2006 04:28 PM.