INT to BINARY string in SQL Server

SQL Server doesn't have a native way of converting INTs to BINARY.

Executing:

SELECT CAST(25 AS BINARY)

in fact returns 19 which is the hexadecimal equivalent, not the binary.

Below, you can see the differences between the three numeric representations:

• Integer: 25
• Binary: 11001
• Hexadecimal: 19

So I had two requirements:

• Have the binary representation of 25 as a string: '11001'
• Be able to set a fixed minimum result size:
  • fixedSize=2: '11001'
  • fixedSize=5: '11001'
  • fixedSize=10: '0000011001'

So I went deep into my brain and pulled out my basic binary conversion knowledge and wrote this scalar function that does just that:

CREATE FUNCTION INT2BIN
(
 @value INT,
 @fixedSize INT = 10
)
RETURNS VARCHAR(1000)
AS
BEGIN
 DECLARE @result VARCHAR(1000) = '';

 WHILE (@value != 0)
 BEGIN
  IF(@value%2 = 0) 
   SET @Result = '0' + @Result;
  ELSE
   SET @Result = '1' + @Result;
   
  SET @value = @value / 2;
 END;

 IF(@FixedSize > 0 AND LEN(@Result) < @FixedSize)
  SET @result = RIGHT('00000000000000000000' + @Result, @FixedSize);

 RETURN @Result;
END
GO

Caution: The above code only supports @FixedSize values equal or below 20. If you need support for higher values, just add more zeros to the 'RIGHT' statement. Another option is to make this padding dynamic by introducing another loop.

CREATE FUNCTION INT2BIN
(
 @value INT,
 @fixedSize INT = 10
)
RETURNS VARCHAR(1000)
AS
BEGIN
 DECLARE @result VARCHAR(1000) = '';

 WHILE (@value != 0)
 BEGIN
  IF(@value%2 = 0) 
   SET @Result = '0' + @Result;
  ELSE
   SET @Result = '1' + @Result;
   
  SET @value = @value / 2;
 END;

 IF(@fixedSize IS NOT NULL AND @fixedSize > 0 AND LEN(@Result) < @fixedSize)
 BEGIN
  DECLARE @len INT = @fixedSize;
  DECLARE @padding VARCHAR(1000) = '';
 
  WHILE @len > 0
  BEGIN
   SET @padding = @padding + '0';
   SET @len = @len-1;
  END; 
  SET @result = RIGHT(@padding + @result, @fixedSize);
 END;
 
 RETURN @result;
END
GO