12,404,895 members (72,010 online)
Technical Blog
alternative version

21.1K views
1 bookmarked
Posted

# INT to BINARY string in SQL Server

, 13 Jun 2011 CPOL
 Rate this:
Converting INTs to BINARY in SQL Server

SQL Server doesn't have a native way of converting `INT`s 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...

 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: 29-Jul-16 0:28 Refresh 1