Click here to Skip to main content
12,404,895 members (72,010 online)
Click here to Skip to main content
Add your own
alternative version

Tagged as

Stats

21.1K views
1 bookmarked
Posted

INT to BINARY string in SQL Server

, 13 Jun 2011 CPOL
Rate this:
Please Sign up or sign in to vote.
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
  • 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

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Share

About the Author

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

Find more at on my blog.

You may also be interested in...

Comments and Discussions

 
GeneralMy vote of 5 Pin
Anurag Gandhi13-Jan-15 19:13
professionalAnurag Gandhi13-Jan-15 19:13 
GeneralThank you. Pin
ksk9-Jun-14 20:57
memberksk9-Jun-14 20:57 
GeneralRe: Thank you. Pin
AlexCode11-Jun-14 20:11
professionalAlexCode11-Jun-14 20:11 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Praise Praise    Rant Rant    Admin Admin   

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

| Advertise | Privacy | Terms of Use | Mobile
Web01 | 2.8.160721.1 | Last Updated 13 Jun 2011
Article Copyright 2011 by AlexCode
Everything else Copyright © CodeProject, 1999-2016
Layout: fixed | fluid