This will convert an integer to a binary string representation.
Range (bigint) -9223372036854775808 to 9223372036854775807
64-bit maximum result. The second parameter specifies the string length of the result.
Examples:
SELECT [dbo].[ConvertIntToBinaryString] (15, 8)
returns 00001111
SELECT [dbo].[ConvertIntToBinaryString] (15, 4)
returns 1111
It works by first converting the number to HEX and then replacing each hex digit with its binary equivalent.
-- *******************************************************************
-- Converts an integer to its binary representation
-- Sample Usage:
-- SELECT [dbo].[ConvertIntToBinaryString] (15, 8)
-- SELECT [dbo].[ConvertIntToBinaryString] (-1, default)
-- *******************************************************************
CREATE FUNCTION [dbo].[ConvertIntToBinaryString]
(
@Numberbigint,
@OutputBits tinyint = 64
)
RETURNS varchar(64)
AS
BEGIN
RETURN RIGHT(
REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(
REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(
CONVERT(varchar(64),CONVERT(binary(8),@Number),2)
, '0','0000'), '1','0001'), '2','0010'), '3','0011'), '4','0100')
, '5','0101'), '6','0110'), '7','0111'), '8','1000') , '9','1001')
, 'A','1010') ,'B','1011') ,'C','1100') ,'D','1101') ,'E','1110') ,'F','1111')
,@OutputBits)
END
GO 
This functions are used to convert integers/numbers into binary format and viceversa.
2016-09-09 (first published: 2014-11-18)
1,883 reads
This function counts the number of 1's in the binary representation of an integer.
2015-07-28 (first published: 2014-02-24)
946 reads