SQL Server将整数转换为二进制字符串

事实上,使用普通的旧SQL非常简单。只需使用位AND。我有点惊讶,没有一个简单的解决方案发布在网上(不涉及UDF)。在我的例子中,我真的想检查位是开还是关(数据来自dotnet枚举)。

    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

产生以下结果:

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

可以将以下内容编码到函数中。您需要删除前导零以满足问题的要求。

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

这是一个通用的基极转换器

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

你能行

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

accepted answer from Sean ,因为我发现它限制了输出中只允许硬编码的位数。在我的日常使用中,我发现只获取最高的1位数或指定期望返回的位数更有用。它会自动在边上填充0,这样它就可以排列成8、16或任何你想要的位数。

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;

由于它是一个内联函数,您不需要“担心”优化器执行的多次读取。

        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*/

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

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

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

标准SQL(在PostgreSQL中测试)。

在SQL Server上,您可以尝试以下示例:

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  

我知道我在这里玩游戏有点晚了,但我最近提出了一个巧妙的解决方案,它利用了一个统计表(类似于上面的@hkravitz解决方案)。关键的区别是,我的解决方案利用了我所谓的 虚拟索引 按降序排列结果的步骤 . 我使用 dbo.rangeAB

请注意,这将按升序返回数字0到30(作为RowNumber的“RN”):

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

它这样做没有排序。RN可以定义为(ORDER BY(SELECT NULL))上方的行数()。同样,按RN排序不需要排序-这是 在玩。

但是,当我尝试降序排序时 做 在执行计划中获取排序。

有限对立面 . RangeAB包含一个名为 Op 有限的 Op 假定 数字所谓“有限对立面”,我的意思是,0是30的对立面,1是29的对立面,等等。。与传统相反的数字不同(-1与1相反)。有限对立面按降序返回。

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

返回:

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

我可以使用Op,我可以利用RN的有限对立面以降序获得数字,同时仍然利用虚拟索引来避免排序。这两个查询返回相同的结果,但是,根据SSM,在比较执行计划时,删除排序将使查询成本降低50倍。

功能

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(''));

兰盖布

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

我相信这个方法简化了很多其他人提出的想法。它使用位运算和 FOR XML 使用CTE生成二进制数字的技巧。

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

您可以使用递归CTE表来实现这一点。在本示例代码中,它设置为16位,但您可以通过更改16->来执行任何长度;你的选择。 此外,要转换的数据是可表抽取的

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

此函数是一个通用转换器,允许将整数转换为任何基本编号系统(如二进制、八进制、十六进制等)的字符串描述。

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

-- 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_INTTOBASEXTR用途:

;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_INTTOBASEXTR结果:

udf\u INTTOBASEXTRU\u最小长度用法:

;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\u INTTOBASEXTRU\u MinLength结果:

想要轻松点吗?做一些逐位的数学运算,算出每个二进制数字。

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

唯一的转折点就是 what Constantin notes

这个版本的价格便宜,使用方便 signed magnitude 在这里,您只需将第一位作为 1 对于没有其他更改的负片。 -123 和 123 只有它们的高位不同。

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

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

请注意,SQL Server INT 支持2 ^-31 到2 ³¹ 所以我们需要循环31次(30到0,包括30次),而不是32次。

为什么不干脆。。。

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]

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;