13,050,617 members (80,201 online)
alternative version

#### Stats

24.9K views
1 bookmarked
Posted 13 Jun 2011

# INT to BINARY string in SQL Server

, 13 Jun 2011
 Rate this:
Converting INTs to BINARY 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

• 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;

WHILE @len > 0
BEGIN
SET @len = @len-1;
END;
SET @result = RIGHT(@padding + @result, @fixedSize);
END;

RETURN @result;
END
GO

## Share

 Architect Switzerland
Senior IT Consultant working in Switzerland as Senior Software Engineer.

Find more at on my blog.

## You may also be interested in...

 Pro

 First Prev Next
 My vote of 5 Anurag Gandhi13-Jan-15 19:13 Anurag Gandhi 13-Jan-15 19:13
 Thank you. ksk9-Jun-14 20:57 ksk 9-Jun-14 20:57
 Re: Thank you. AlexCode11-Jun-14 20:11 AlexCode 11-Jun-14 20:11
 Last Visit: 31-Dec-99 18:00     Last Update: 25-Jul-17 16:28 Refresh 1

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.

Web01 | 2.8.170713.1 | Last Updated 13 Jun 2011