15,610,839 members
Articles / Database Development / SQL Server
Article
Posted 13 Jun 2011

50.4K views
1 bookmarked

INT to BINARY string in SQL Server

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

SQL Server doesn't have a native way of converting `INT`s to `BINARY`.

Executing:

SQL
`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`

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

SQL
```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.

SQL
```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```

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

Find more at on my blog.