SQL Server Convert integer to binary string

Question

I was wondering if there was an easy way in SQL to convert an integer to its binary representation and then store it as a varchar.

For example 5 would be converted to "101" and stored as a varchar.

Answer 1

declare @i int = 987667
declare @stringbit varchar(32)
declare @1in varchar(16) = '0123456789ABCDEF', @1ou varchar(16) = 0x00010203101112132021222330313233
declare @2in varchar(4)  = '0123',             @2ou varchar(4)  = 0x00011011
set @stringbit =
convert(varchar(32), convert(varbinary(32), translate(
convert(varchar(32), convert(varbinary(32), translate(
convert(varchar(32), convert(varbinary(32), @i), 2),@1in, @1ou)), 2),@2in, 
@2ou)), 2)

print @stringbit

Answer 2

I didn't like any of the answers (either to complex or not general enough). Here's what I made instead:

replace(replace(replace(replace(
replace(replace(replace(replace(
replace(replace(replace(replace(
replace(replace(replace(replace(
    convert(varchar(8), convert(binary(4), example), 2), 
'0', '0000'), '1', '0001'), '2', '0010'), '3', '0011'),
'4', '0100'), '5', '0101'), '6', '0110'), '7', '0111'),
'8', '1000'), '9', '1001'), 'A', '1010'), 'B', '1011'),
'C', '1100'), 'D', '1101'), 'E', '1110'), 'F', '1111')

If you want to convert bigint just convert to binary(8) and varchar(16) instead. (Other data types are left as an excercise for the reader.)

Performance? Well, it converted 25,000 values in less than a second, which was good enough for my purpose.

Answer 3

Why not simply...

declare @num int = 75

select
@num [Dec]
, convert (varchar(1), @num / 128 % 2)
+ convert (varchar(1), @num / 64 % 2)
+ convert (varchar(1), @num / 32 % 2)
+ convert (varchar(1), @num / 16 % 2)
+ convert (varchar(1), @num / 8 % 2)
+ convert (varchar(1), @num / 4 % 2)
+ convert (varchar(1), @num / 2 % 2)
+ convert (varchar(1), @num % 2) as [Bin]

Answer 4

Want easy? Do some bitwise math to map out each binary digit.

CREATE FUNCTION dbo.BinaryRep (@val INT)
RETURNS VARCHAR(32)
WITH EXECUTE AS CALLER
AS

BEGIN
    DECLARE @ret VARCHAR(32)
    DECLARE @cnt INT = 30; -- 30 to 0 inclusive in loop
    
    -- handle negative (we're using signed magnitude because that's simple)
    SET @ret = IIF(@val < 0, '1', '0');
    SET @val = ABS(@val); -- totally cheating here.
    
    -- bitwise masking madness, one digit at a time.   
    WHILE @cnt > -1
    BEGIN
        SET @ret = CONCAT(@ret, IIF(@val & POWER(2, @cnt) = 0, 0, 1));
        SET @cnt = @cnt - 1;
    END;
    
    RETURN @ret;
END

The only twist is exactly what Constantin notes: How do you like your negatives?

This version cheaps out and uses signed magnitude where you just have the first bit as 1 for negatives with no other changes. -123 and 123 only differ by their high bits.

select dbo.BinaryRep(123) as plus, dbo.BinaryRep(-123) as minus

plus                             minus
-------------------------------- --------------------------------
00000000000000000000000001111011 10000000000000000000000001111011

Note that SQL Server INT supports 2^-31 to 2³¹ so we need to loop through 31 times (30 to 0, inclusive), not 32.

Answer 5

This function is a generic convertor, allowing an integer to be converted to a string depiction for any base numbering system, like binary, octal, hexadecimal, etc.

-- specify a string and numbering system Base value, for example 16 for hexadecimal
CREATE FUNCTION udf_IntToBaseXStr(@baseVal BIGINT,
                                    @baseX   BIGINT)
returns VARCHAR(63)
AS
    BEGIN
        --bigint : -2^63 (-9,223,372,036,854,775,808) to 2^63-1 (9,223,372,036,854,775,807)  
        --         or 63 ones (1111111,11111111,11111111,11111111,11111111,11111111,11111111,11111111) in binary
        DECLARE @val BIGINT -- value from all
        DECLARE @cv BIGINT -- value from a single char
        DECLARE @baseStr VARCHAR(63)
        SET @baseStr = '';
        -- assumes a numbering method of 0123456789ABCDEF..... 
        SET @val = @baseVal
        WHILE ( @val > 0 )
            BEGIN
                SET @cv = @val % @basex -- calculate the right most char's value
                SET @baseStr = -- add it to (any existing) string
                CASE
                    WHEN @cv < 10 THEN Char(Ascii('0') + @cv)
                    ELSE Char(Ascii('A') + ( @cv - 10 ))
                END
                + @baseStr
                SET @val = ( @val - @cv ) / @basex
            END
        RETURN @baseStr
    END
GO

If you need to guarantee a minimum length, the next function wraps the above function, prepending a number of ZEROES, forcing the returned string to your desired minimum length. It does not truncate to the specified length.

-- specify a string and numbering system Base value, for example, 16 for hexadecimal
-- prepends LEADING ZEROS to force length of returned string to be AT LEAST minLength chars
CREATE FUNCTION udf_IntToBaseXStr_MinLength(@baseVal   BIGINT,
                                            @baseX     BIGINT,
                                            @minLength INT)
returns VARCHAR(63)
AS
    BEGIN
        DECLARE @baseStr VARCHAR(63)
        SET @baseStr = dbo.udf_IntToBaseXStr(@baseVal, @baseX)
        IF Len(@baseStr) < @minLength
            SET @baseStr = Replicate('0', @minLength - Len(@baseStr))
                                         + @baseStr
        RETURN @baseStr
    END
GO

udf_IntToBaseXStr Usage:

;with CTE as 
(
    SELECT BaseX = 2, AKA = 'binary' 
    UNION SELECT 8, 'octal' 
    UNION SELECT 10, 'decimal' 
    UNION SELECT 15, 'pentadecimal' 
    UNION SELECT 16, 'hexadecimal' 
)
SELECT BaseX, AKA, Result = dbo.udf_IntToBaseXStr(328239523, BaseX) FROM CTE

udf_IntToBaseXStr Result:

BaseX	AKA	Result
2	binary	10011100100001000100110100011
8	octal	2344104643
10	decimal	328239523
15	pentadecimal	1DC3B24D
16	hexadecimal	139089A3

udf_IntToBaseXStr_MinLength Usage:

;with CTE as 
(
    SELECT BaseX = 2, AKA = 'binary' 
    UNION SELECT 8, 'octal' 
    UNION SELECT 10, 'decimal' 
    UNION SELECT 15, 'pentadecimal' 
    UNION SELECT 16, 'hexadecimal' 
)
SELECT BaseX, AKA, Result = dbo.udf_IntToBaseXStr_MinLength(328239523, BaseX, 24) FROM CTE

udf_IntToBaseXStr_MinLength Result:

BaseX	AKA	Result
2	binary	10011100100001000100110100011
8	octal	000000000000002344104643
10	decimal	000000000000000328239523
15	pentadecimal	00000000000000001DC3B24D
16	hexadecimal	0000000000000000139089A3

Answer 6

You can use a recursive CTE table to do this. In this example code, it is set for 16 bits, but you can do any length by changing 16-> your choice. Also, the data you want to convert is table DecimalTable

WITH DecimalTable AS (SELECT 10 decimal_num UNION SELECT 20),
     DtoB AS (SELECT decimal_num
                     ,1 n
                     ,CAST(CAST(decimal_num%2 AS bit) AS VARCHAR(16)) binary_num
               FROM DecimalTable 
               UNION ALL
               SELECT decimal_num
                     ,n*2 n
                     ,CAST(CONCAT(CAST(decimal_num&n as bit), binary_num)
                           AS VARCHAR(16)) binary_num
                FROM DtoB
                WHERE n<POWER(2,16))

    SELECT decimal_num, binary_num
    FROM DtoB

Answer 7

I know I'm a bit late to the game here but I recently came up with a slick solution for this that leverages a tally table (similar to @hkravitz solution above.) The key difference is that my leverages what I call the Virtual Index to sort the results in descending order without a sort operator in the execution plan. I accomplish this using dbo.rangeAB which is included at the end of this post.

Note that this returns the numbers 0 to 30 (as "RN" for RowNumber) in ascending order:

SELECT   r.RN
FROM     dbo.rangeAB(0,30,1,0) AS r
ORDER BY r.RN;

It does so without sorting. RN can be defined as ROW_NUMBER() OVER (ORDER BY (SELECT NULL)). Sorting by RN does not require a sort, again - that's the virtual index at play.

When I try a descending sort however I do get a sort in the execution plan.

Enter Finite Opposites. RangeAB includes a column named Op - OP RN's Finite Opposite Number. By "finite opposite" I mean, 0 is the opposite of 30, 1 is the opposite of 29, etc.. Unlike traditional opposite numbers (-1 is opposite of 1). Finite opposites are returned in descending order.

SELECT   r.RN, r.OP
FROM     dbo.rangeAB(0,30,1,0) AS r
ORDER BY r.RN;

Returns:

RN    OP
----- -------
0     30
1     29
2     28
3     27
....
27    3
28    2
29    1
30    0

I can use Op I can leverage RN's finite opposite to get the numbers in descending order while still leveraging the virtual index to avoid a sort. These two queries return the same thing but, when comparing execution plans, according to SSMS removing the sort reduces the query cost by a factor of 50X.

THE FUNCTION

CREATE FUNCTION dbo.NumberToBinary(@input INT)
RETURNS TABLE WITH SCHEMABINDING AS RETURN
/* Created By Alan Burstein 20191112, Requires RangeAB (code below) */
SELECT BIN = (
  SELECT       @input/f.Np2%2
  FROM        dbo.rangeAB(0,30,1,0)   AS r
  CROSS APPLY (VALUES(POWER(2,r.Op))) AS f(NP2)
  WHERE       (@input = 0 AND f.Np2 = 1) OR @input >= f.Np2
  ORDER BY     ROW_NUMBER() OVER (ORDER BY (SELECT NULL))
  FOR XML PATH(''));

RangeAB

CREATE FUNCTION dbo.rangeAB
(
  @low  bigint, 
  @high bigint, 
  @gap  bigint,
  @row1 bit
)
/****************************************************************************************
[Purpose]:
 Creates up to 531,441,000,000 sequentia1 integers numbers beginning with @low and ending 
 with @high. Used to replace iterative methods such as loops, cursors and recursive CTEs 
 to solve SQL problems. Based on Itzik Ben-Gan's getnums function with some tweeks and 
 enhancements and added functionality. The logic for getting rn to begin at 0 or 1 is 
 based comes from Jeff Moden's fnTally function. 

 The name range because it's similar to clojure's range function. The name "rangeAB" as 
 used because "range" is a reserved SQL keyword.

[Author]: Alan Burstein

[Compatibility]: 
 SQL Server 2008+ and Azure SQL Database

[Syntax]:
 SELECT r.RN, r.OP, r.N1, r.N2
 FROM dbo.rangeAB(@low,@high,@gap,@row1) AS r;

[Parameters]:
 @low  = a bigint that represents the lowest value for n1.
 @high = a bigint that represents the highest value for n1.
 @gap  = a bigint that represents how much n1 and n2 will increase each row; @gap also
         represents the difference between n1 and n2.
 @row1 = a bit that represents the first value of rn. When @row = 0 then rn begins
         at 0, when @row = 1 then rn will begin at 1.

[Returns]:
 Inline Table Valued Function returns:
 rn = bigint; a row number that works just like T-SQL ROW_NUMBER() except that it can 
      start at 0 or 1 which is dictated by @row1.
 op = bigint; returns the "opposite number that relates to rn. When rn begins with 0 and
      ends with 10 then 10 is the opposite of 0, 9 the opposite of 1, etc. When rn begins
      with 1 and ends with 5 then 1 is the opposite of 5, 2 the opposite of 4, etc...
 n1 = bigint; a sequential number starting at the value of @low and incrimentingby the
      value of @gap until it is less than or equal to the value of @high.
 n2 = bigint; a sequential number starting at the value of @low+@gap and  incrimenting 
      by the value of @gap.

[Dependencies]:
N/A

[Developer Notes]:

 1. The lowest and highest possible numbers returned are whatever is allowable by a 
    bigint. The function, however, returns no more than 531,441,000,000 rows (8100^3). 
 2. @gap does not affect rn, rn will begin at @row1 and increase by 1 until the last row
    unless its used in a query where a filter is applied to rn.
 3. @gap must be greater than 0 or the function will not return any rows.
 4. Keep in mind that when @row1 is 0 then the highest row-number will be the number of
    rows returned minus 1
 5. If you only need is a sequential set beginning at 0 or 1 then, for best performance
    use the RN column. Use N1 and/or N2 when you need to begin your sequence at any 
    number other than 0 or 1 or if you need a gap between your sequence of numbers. 
 6. Although @gap is a bigint it must be a positive integer or the function will
    not return any rows.
 7. The function will not return any rows when one of the following conditions are true:
      * any of the input parameters are NULL
      * @high is less than @low 
      * @gap is not greater than 0
    To force the function to return all NULLs instead of not returning anything you can
    add the following code to the end of the query:

      UNION ALL 
      SELECT NULL, NULL, NULL, NULL
      WHERE NOT (@high&@low&@gap&@row1 IS NOT NULL AND @high >= @low AND @gap > 0)

    This code was excluded as it adds a ~5% performance penalty.
 8. There is no performance penalty for sorting by rn ASC; there is a large performance 
    penalty for sorting in descending order WHEN @row1 = 1; WHEN @row1 = 0
    If you need a descending sort the use op in place of rn then sort by rn ASC. 

Best Practices:
--===== 1. Using RN (rownumber)
 -- (1.1) The best way to get the numbers 1,2,3...@high (e.g. 1 to 5):
 SELECT RN FROM dbo.rangeAB(1,5,1,1);
 -- (1.2) The best way to get the numbers 0,1,2...@high-1 (e.g. 0 to 5):
 SELECT RN FROM dbo.rangeAB(0,5,1,0);

--===== 2. Using OP for descending sorts without a performance penalty
 -- (2.1) The best way to get the numbers 5,4,3...@high (e.g. 5 to 1):
 SELECT op FROM dbo.rangeAB(1,5,1,1) ORDER BY rn ASC;
 -- (2.2) The best way to get the numbers 0,1,2...@high-1 (e.g. 5 to 0):
 SELECT op FROM dbo.rangeAB(1,6,1,0) ORDER BY rn ASC;

--===== 3. Using N1
 -- (3.1) To begin with numbers other than 0 or 1 use N1 (e.g. -3 to 3):
 SELECT N1 FROM dbo.rangeAB(-3,3,1,1);
 -- (3.2) ROW_NUMBER() is built in. If you want a ROW_NUMBER() include RN:
 SELECT RN, N1 FROM dbo.rangeAB(-3,3,1,1);
 -- (3.3) If you wanted a ROW_NUMBER() that started at 0 you would do this:
 SELECT RN, N1 FROM dbo.rangeAB(-3,3,1,0);

--===== 4. Using N2 and @gap
 -- (4.1) To get 0,10,20,30...100, set @low to 0, @high to 100 and @gap to 10:
 SELECT N1 FROM dbo.rangeAB(0,100,10,1);
 -- (4.2) Note that N2=N1+@gap; this allows you to create a sequence of ranges.
 --       For example, to get (0,10),(10,20),(20,30).... (90,100):
 SELECT N1, N2 FROM dbo.rangeAB(0,90,10,1);
 -- (4.3) Remember that a rownumber is included and it can begin at 0 or 1:
 SELECT RN, N1, N2 FROM dbo.rangeAB(0,90,10,1);

[Examples]:
--===== 1. Generating Sample data (using rangeAB to create "dummy rows")
 -- The query below will generate 10,000 ids and random numbers between 50,000 and 500,000
 SELECT
   someId    = r.rn,
   someNumer = ABS(CHECKSUM(NEWID())%450000)+50001 
 FROM rangeAB(1,10000,1,1) r;

--===== 2. Create a series of dates; rn is 0 to include the first date in the series
 DECLARE @startdate DATE = '20180101', @enddate DATE = '20180131';

 SELECT r.rn, calDate = DATEADD(dd, r.rn, @startdate)
 FROM dbo.rangeAB(1, DATEDIFF(dd,@startdate,@enddate),1,0) r;
 GO

--===== 3. Splitting (tokenizing) a string with fixed sized items
 -- given a delimited string of identifiers that are always 7 characters long
 DECLARE @string VARCHAR(1000) = 'A601225,B435223,G008081,R678567';

 SELECT
   itemNumber = r.rn, -- item's ordinal position 
   itemIndex  = r.n1, -- item's position in the string (it's CHARINDEX value)
   item       = SUBSTRING(@string, r.n1, 7) -- item (token)
 FROM dbo.rangeAB(1, LEN(@string), 8,1)  r;
 GO

--===== 4. Splitting (tokenizing) a string with random delimiters
 DECLARE @string VARCHAR(1000) = 'ABC123,999F,XX,9994443335';

 SELECT
   itemNumber = ROW_NUMBER() OVER (ORDER BY r.rn), -- item's ordinal position 
   itemIndex  = r.n1+1, -- item's position in the string (it's CHARINDEX value)
   item       = SUBSTRING
               (
                 @string,
                 r.n1+1,
                 ISNULL(NULLIF(CHARINDEX(',',@string,r.n1+1),0)-r.n1-1, 8000)
               ) -- item (token)
 FROM dbo.rangeAB(0,DATALENGTH(@string),1,1) r
 WHERE SUBSTRING(@string,r.n1,1) = ',' OR r.n1 = 0;
 -- logic borrowed from: http://www.sqlservercentral.com/articles/Tally+Table/72993/

--===== 5. Grouping by a weekly intervals
 -- 5.1. how to create a series of start/end dates between @startDate & @endDate
 DECLARE @startDate DATE = '1/1/2015', @endDate DATE = '2/1/2015';
 SELECT 
   WeekNbr   = r.RN,
   WeekStart = DATEADD(DAY,r.N1,@StartDate), 
   WeekEnd   = DATEADD(DAY,r.N2-1,@StartDate)
 FROM dbo.rangeAB(0,datediff(DAY,@StartDate,@EndDate),7,1) r;
 GO

 -- 5.2. LEFT JOIN to the weekly interval table
 BEGIN
  DECLARE @startDate datetime = '1/1/2015', @endDate datetime = '2/1/2015';
  -- sample data 
  DECLARE @loans TABLE (loID INT, lockDate DATE);
  INSERT @loans SELECT r.rn, DATEADD(dd, ABS(CHECKSUM(NEWID())%32), @startDate)
  FROM dbo.rangeAB(1,50,1,1) r;

  -- solution 
  SELECT 
    WeekNbr   = r.RN,
    WeekStart = dt.WeekStart, 
    WeekEnd   = dt.WeekEnd,
    total     = COUNT(l.lockDate)
  FROM dbo.rangeAB(0,datediff(DAY,@StartDate,@EndDate),7,1) r
  CROSS APPLY (VALUES (
    CAST(DATEADD(DAY,r.N1,@StartDate) AS DATE), 
    CAST(DATEADD(DAY,r.N2-1,@StartDate) AS DATE))) dt(WeekStart,WeekEnd)
  LEFT JOIN @loans l ON l.lockDate BETWEEN  dt.WeekStart AND dt.WeekEnd
  GROUP BY r.RN, dt.WeekStart, dt.WeekEnd ;
 END;

--===== 6. Identify the first vowel and last vowel in a along with their positions
 DECLARE @string VARCHAR(200) = 'This string has vowels';

 SELECT TOP(1) position = r.rn, letter = SUBSTRING(@string,r.rn,1)
 FROM dbo.rangeAB(1,LEN(@string),1,1) r
 WHERE SUBSTRING(@string,r.rn,1) LIKE '%[aeiou]%'
 ORDER BY r.rn;

 -- To avoid a sort in the execution plan we'll use op instead of rn
 SELECT TOP(1) position = r.op, letter = SUBSTRING(@string,r.op,1)
 FROM dbo.rangeAB(1,LEN(@string),1,1) r
 WHERE SUBSTRING(@string,r.rn,1) LIKE '%[aeiou]%'
 ORDER BY r.rn;

---------------------------------------------------------------------------------------
[Revision History]:
 Rev 00 - 20140518 - Initial Development - Alan Burstein
 Rev 01 - 20151029 - Added 65 rows to make L1=465; 465^3=100.5M. Updated comment section
                   - Alan Burstein
 Rev 02 - 20180613 - Complete re-design including opposite number column (op)
 Rev 03 - 20180920 - Added additional CROSS JOIN to L2 for 530B rows max - Alan Burstein
****************************************************************************************/
RETURNS TABLE WITH SCHEMABINDING AS RETURN
WITH L1(N) AS 
(
  SELECT 1
  FROM (VALUES
   (0),(0),(0),(0),(0),(0),(0),(0),(0),(0),(0),(0),(0),(0),(0),(0),(0),(0),(0),(0),(0),(0),
   (0),(0),(0),(0),(0),(0),(0),(0),(0),(0),(0),(0),(0),(0),(0),(0),(0),(0),(0),(0),(0),(0),
   (0),(0),(0),(0),(0),(0),(0),(0),(0),(0),(0),(0),(0),(0),(0),(0),(0),(0),(0),(0),(0),(0),
   (0),(0),(0),(0),(0),(0),(0),(0),(0),(0),(0),(0),(0),(0),(0),(0),(0),(0),(0),(0),(0),(0),
   (0),(0)) T(N) -- 90 values 
),
L2(N)  AS (SELECT 1 FROM L1 a CROSS JOIN L1 b CROSS JOIN L1 c),
iTally AS (SELECT rn = ROW_NUMBER() OVER (ORDER BY (SELECT 1)) FROM L2 a CROSS JOIN L2 b)
SELECT
  r.RN,
  r.OP,
  r.N1,
  r.N2
FROM
(
  SELECT
    RN = 0,
    OP = (@high-@low)/@gap,
    N1 = @low,
    N2 = @gap+@low
  WHERE @row1 = 0
  UNION ALL -- ISNULL required in the TOP statement below for error handling purposes
  SELECT TOP (ABS((ISNULL(@high,0)-ISNULL(@low,0))/ISNULL(@gap,0)+ISNULL(@row1,1)))
    RN = i.rn,
    OP = (@high-@low)/@gap+(2*@row1)-i.rn,
    N1 = (i.rn-@row1)*@gap+@low,
    N2 = (i.rn-(@row1-1))*@gap+@low
  FROM iTally AS i
  ORDER BY i.rn
) AS r
WHERE @high&@low&@gap&@row1 IS NOT NULL AND @high >= @low AND @gap > 0;
GO

Answer 8

Here's a bit of a change to the accepted answer from Sean, since I found it limiting to only allow a hardcoded number of digits in the output. In my daily use, I find it more useful to either get only up to the highest 1 digit, or specify how many digits I'm expecting back. It will automatically pad the side with 0s, so that it lines up to 8, 16, or whatever number of bits you want.

Create function f_DecimalToBinaryString
    (
        @Dec int,
        @MaxLength int = null
    )
Returns varchar(max)
as Begin

    Declare @BinStr varchar(max) = '';

    -- Perform the translation from Dec to Bin
    While @Dec > 0 Begin

        Set @BinStr = Convert(char(1), @Dec % 2) + @BinStr;
        Set @Dec = Convert(int, @Dec /2);

    End;

    -- Either pad or trim the output to match the number of digits specified.
    If (@MaxLength is not null) Begin
        If @MaxLength <= Len(@BinStr) Begin -- Trim down
            Set @BinStr = SubString(@BinStr, Len(@BinStr) - (@MaxLength - 1), @MaxLength);
        End Else Begin -- Pad up
            Set @BinStr = Replicate('0', @MaxLength - Len(@BinStr)) + @BinStr;
        End;
    End;

    Return @BinStr;

End;

Answer 9

On SQL Server, you can try something like the sample below:

DECLARE  @Int int = 321

SELECT @Int
,CONCAT
(CAST(@Int & power(2,15)    AS bit)
,CAST(@Int & power(2,14)    AS bit)
,CAST(@Int & power(2,13)    AS bit)
,CAST(@Int & power(2,12)    AS bit)
,CAST(@Int & power(2,11)    AS bit)
,CAST(@Int & power(2,10)    AS bit)
,CAST(@Int & power(2,9)     AS bit)
,CAST(@Int & power(2,8)     AS bit)
,CAST(@Int & power(2,7)     AS bit)
,CAST(@Int & power(2,6)     AS bit)
,CAST(@Int & power(2,5)     AS bit)
,CAST(@Int & power(2,4)     AS bit)
,CAST(@Int & power(2,3)     AS bit)
,CAST(@Int & power(2,2)     AS bit)
,CAST(@Int & power(2,1)     AS bit)
,CAST(@Int & power(2,0)     AS bit) ) AS BitString

,CAST(@Int & power(2,15)    AS bit) AS BIT15
,CAST(@Int & power(2,14)    AS bit) AS BIT14
,CAST(@Int & power(2,13)    AS bit) AS BIT13
,CAST(@Int & power(2,12)    AS bit) AS BIT12
,CAST(@Int & power(2,11)    AS bit) AS BIT11
,CAST(@Int & power(2,10)    AS bit) AS BIT10
,CAST(@Int & power(2,9)     AS bit) AS BIT9 
,CAST(@Int & power(2,8)     AS bit) AS BIT8 
,CAST(@Int & power(2,7)     AS bit) AS BIT7 
,CAST(@Int & power(2,6)     AS bit) AS BIT6 
,CAST(@Int & power(2,5)     AS bit) AS BIT5 
,CAST(@Int & power(2,4)     AS bit) AS BIT4 
,CAST(@Int & power(2,3)     AS bit) AS BIT3 
,CAST(@Int & power(2,2)     AS bit) AS BIT2 
,CAST(@Int & power(2,1)     AS bit) AS BIT1 
,CAST(@Int & power(2,0)     AS bit) AS BIT0  

Answer 10

with t as (select * from (values (0),(1)) as t(c)),

t0 as (table t),
t1 as (table t),
t2 as (table t),
t3 as (table t),
t4 as (table t),
t5 as (table t),
t6 as (table t),
t7 as (table t),
t8 as (table t),
t9 as (table t),
ta as (table t),
tb as (table t),
tc as (table t),
td as (table t),
te as (table t),
tf as (table t)

select  '' || t0.c || t1.c || t2.c || t3.c || t4.c || t5.c || t6.c || t7.c || t8.c || t9.c || ta.c || tb.c || tc.c || td.c || te.c || tf.c as n
from  t0,t1,t2,t3,t4,t5,t6,t7,t8,t9,ta,tb,tc,td,te,tf
order by n 

limit 1 offset 5

Standard SQL (tested in PostgreSQL).

Answer 11

I used the following ITVF function to convert from decimal to Binary as it is a inline function you don't need to "worry" about multiple reads performed by the optimizer.

        CREATE FUNCTION dbo.udf_DecimalToBinary 
    (
        @Decimal VARCHAR(32)
    )

    RETURNS TABLE AS RETURN

    WITH Tally (n) AS
    (
        --32 Rows
        SELECT TOP 30 ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) -1
        FROM (VALUES (0),(0),(0),(0)) a(n)
        CROSS JOIN (VALUES(0),(0),(0),(0),(0),(0),(0),(0)) b(n)

     ) 

     , Anchor (n, divisor , Result) as 
     (
     SELECT t.N , 
            CONVERT(BIGINT, @Decimal) / POWER(2,T.N) ,  
            CONVERT(BIGINT, @Decimal) / POWER(2,T.N) % 2 
     FROM Tally t 
     WHERE CONVERT(bigint,@Decimal) >= POWER(2,t.n)
     )


     SELECT TwoBaseBinary =  '' +
        (SELECT Result 
          FROM Anchor
          ORDER BY N DESC 
          FOR XML PATH ('') , TYPE).value('.','varchar(200)')

   /*How to use*/
   SELECT TwoBaseBinary 
   FROM dbo.udf_DecimalToBinary ('1234')

   /*result -> 10011010010*/

Answer 12

I believe that this method simplifies a lot of the other ideas that others have presented. It uses bitwise arithmetic along with the FOR XML trick with a CTE to generate the binary digits.

DECLARE @my_int INT = 5

;WITH CTE_Binary AS
(
    SELECT 1 AS seq, 1 AS val
    UNION ALL
    SELECT seq + 1 AS seq, power(2, seq)
    FROM CTE_Binary
    WHERE
        seq < 8
)
SELECT
(
    SELECT
        CAST(CASE WHEN B2.seq IS NOT NULL THEN 1 ELSE 0 END AS CHAR(1))
    FROM
        CTE_Binary B1
    LEFT OUTER JOIN CTE_Binary B2 ON
        B2.seq = B1.seq AND
        @my_int & B2.val = B2.val
    ORDER BY
        B1.seq DESC
    FOR XML PATH('')
) AS val

Answer 13

How about this...

SELECT number_value
,MOD(number_value / 32768, 2) AS BIT15
,MOD(number_value / 16384, 2) AS BIT14
,MOD(number_value /  8192, 2) AS BIT13
,MOD(number_value /  4096, 2) AS BIT12
,MOD(number_value /  2048, 2) AS BIT11
,MOD(number_value /  1024, 2) AS BIT10
,MOD(number_value /   512, 2) AS BIT9 
,MOD(number_value /   256, 2) AS BIT8 
,MOD(number_value /   128, 2) AS BIT7 
,MOD(number_value /    64, 2) AS BIT6 
,MOD(number_value /    32, 2) AS BIT5 
,MOD(number_value /    16, 2) AS BIT4 
,MOD(number_value /     8, 2) AS BIT3 
,MOD(number_value /     4, 2) AS BIT2 
,MOD(number_value /     2, 2) AS BIT1 
,MOD(number_value      , 2) AS BIT0 
FROM your_table;

Answer 14

Actually this is REALLY SIMPLE using plain old SQL. Just use bitwise ANDs. I was a bit amazed that there wasn't a simple solution posted online (that didn't invovled UDFs). In my case I really wanted to check if bits were on or off (the data is coming from dotnet eNums).

Accordingly here is an example that will give you seperately and together - bit values and binary string (the big union is just a hacky way of producing numbers that will work accross DBs:

    select t.Number
    , cast(t.Number & 64 as bit) as bit7
    , cast(t.Number & 32 as bit) as bit6
    , cast(t.Number & 16 as bit) as bit5
    , cast(t.Number & 8 as bit) as bit4
    , cast(t.Number & 4 as bit) as bit3
    , cast(t.Number & 2 as bit)  as bit2
    ,cast(t.Number & 1 as bit) as bit1

    , cast(cast(t.Number & 64 as bit) as CHAR(1)) 
    +cast( cast(t.Number & 32 as bit) as CHAR(1))
    +cast( cast(t.Number & 16 as bit)  as CHAR(1))
    +cast( cast(t.Number & 8 as bit)  as CHAR(1))
    +cast( cast(t.Number & 4 as bit)  as CHAR(1))
    +cast( cast(t.Number & 2 as bit)   as CHAR(1))
    +cast(cast(t.Number & 1 as bit)  as CHAR(1)) as binary_string
    --to explicitly answer the question, on MSSQL without using REGEXP (which would make it simple)
    ,SUBSTRING(cast(cast(t.Number & 64 as bit) as CHAR(1)) 
                    +cast( cast(t.Number & 32 as bit) as CHAR(1))
                    +cast( cast(t.Number & 16 as bit)  as CHAR(1))
                    +cast( cast(t.Number & 8 as bit)  as CHAR(1))
                    +cast( cast(t.Number & 4 as bit)  as CHAR(1))
                    +cast( cast(t.Number & 2 as bit)   as CHAR(1))
                    +cast(cast(t.Number & 1 as bit)  as CHAR(1))
                    ,
                    PATINDEX('%1%', cast(cast(t.Number & 64 as bit) as CHAR(1)) 
                                        +cast( cast(t.Number & 32 as bit) as CHAR(1))
                                        +cast( cast(t.Number & 16 as bit)  as CHAR(1))
                                        +cast( cast(t.Number & 8 as bit)  as CHAR(1))
                                        +cast( cast(t.Number & 4 as bit)  as CHAR(1))
                                        +cast( cast(t.Number & 2 as bit)   as CHAR(1))
                                        +cast(cast(t.Number & 1 as bit)  as CHAR(1)  )
                    )
,99)


from (select 1 as Number union all select 2 union all select 3 union all select 4 union all select 5 union all select 6 
    union all select 7 union all select 8 union all select 9 union all select 10) as t

Produces this result:

num  bit7 bit6 bit5 bit4 bit3 bit2 bit1 binary_string   binary_string_trimmed 
1    0    0    0    0    0    0    1    0000001         1
2    0    0    0    0    0    1    0    0000010         10
3    0    0    0    0    0    1    1    0000011         11
4    0    0    0    1    0    0    0    0000100         100
5    0    0    0    0    1    0    1    0000101         101
6    0    0    0    0    1    1    0    0000110         110
7    0    0    0    0    1    1    1    0000111         111
8    0    0    0    1    0    0    0    0001000         1000
9    0    0    0    1    0    0    1    0001001         1001
10   0    0    0    1    0    1    0    0001010         1010

Answer 15

this is a generic base converter

http://dpatrickcaldwell.blogspot.com/2009/05/converting-decimal-to-hexadecimal-with.html

you can do

select reverse(dbo.ConvertToBase(5, 2))   -- 101

Answer 16

declare @intVal Int 
set @intVal = power(2,12)+ power(2,5) + power(2,1);
With ComputeBin (IntVal, BinVal,FinalBin)
As
    (
    Select @IntVal IntVal, @intVal %2 BinVal , convert(nvarchar(max),(@intVal %2 ))     FinalBin
    Union all
    Select IntVal /2, (IntVal /2) %2, convert(nvarchar(max),(IntVal /2) %2) + FinalBin     FinalBin
    From ComputeBin
    Where IntVal /2 > 0
)
select FinalBin from ComputeBin where intval = ( select min(intval) from ComputeBin);

Answer 17

declare @i int /* input */
set @i = 42

declare @result varchar(32) /* SQL Server int is 32 bits wide */
set @result = ''
while 1 = 1 begin
  select @result = convert(char(1), @i % 2) + @result,
         @i = convert(int, @i / 2)
  if @i = 0 break
end

select @result

Answer 18

Following could be coded into a function. You would need to trim off leading zeros to meet requirements of your question.

declare @intvalue int
set @intvalue=5

declare @vsresult varchar(64)
declare @inti int
select @inti = 64, @vsresult = ''
while @inti>0
  begin
    select @vsresult=convert(char(1), @intvalue % 2)+@vsresult
    select @intvalue = convert(int, (@intvalue / 2)), @inti=@inti-1
  end
select @vsresult