blocks|key|1529494|text|下面的代码可以编码到一个函数中。您需要修剪前导零以满足您的问题的要求。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1529495|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+%25+2)%2B@vsresult
++++select+@intvalue+=+convert(int,+(@intvalue+/+2)),+@inti=@inti-1
++end
select+@vsresult|code-block|syntax|javascript|1529496|entityMap^0|0|0^^$0|@$1|2|3|4|5|6|7|I|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|J|8|@]|9|@]|A|$E|F]]|$1|G|3|-4|5|6|7|K|8|@]|9|@]|A|$]]]|H|$]]

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

<pre><code>declare @intvalue int
set @intvalue=5

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

blocks|key|1744455|text|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+%25+2)+%2B+@result,
+++++++++@i+=+convert(int,+@i+/+2)
++if+@i+=+0+break
end

select+@result|type|code-block|depth|inlineStyleRanges|entityRanges|data|syntax|javascript|1744456|unstyled|entityMap^0|0^^$0|@$1|2|3|4|5|6|7|G|8|@]|9|@]|A|$B|C]]|$1|D|3|-4|5|E|7|H|8|@]|9|@]|A|$]]]|F|$]]

<pre><code>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
</code></pre>

blocks|key|1529512|text|declare+@intVal+Int+
set+@intVal+=+power(2,12)%2B+power(2,5)+%2B+power(2,1);
With+ComputeBin+(IntVal,+BinVal,FinalBin)
As
++++(
++++Select+@IntVal+IntVal,+@intVal+%252+BinVal+,+convert(nvarchar(max),(@intVal+%252+))+++++FinalBin
++++Union+all
++++Select+IntVal+/2,+(IntVal+/2)+%252,+convert(nvarchar(max),(IntVal+/2)+%252)+%2B+FinalBin+++++FinalBin
++++From+ComputeBin
++++Where+IntVal+/2+>+0
)
select+FinalBin+from+ComputeBin+where+intval+=+(+select+min(intval)+from+ComputeBin);|type|code-block|depth|inlineStyleRanges|entityRanges|data|syntax|javascript|1529513|unstyled|entityMap^0|0^^$0|@$1|2|3|4|5|6|7|G|8|@]|9|@]|A|$B|C]]|$1|D|3|-4|5|E|7|H|8|@]|9|@]|A|$]]]|F|$]]

<pre><code>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 &gt; 0
)
select FinalBin from ComputeBin where intval = ( select min(intval) from ComputeBin);
</code></pre>

blocks|key|1529526|text|这是一个通用的基本转换器|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1529527|http://dpatrickcaldwell.blogspot.com/2009/05/converting-decimal-to-hexadecimal-with.html|offset|length|1529528|你可以做到|1529529|select+reverse(dbo.ConvertToBase(5,+2))+++--+101|code-block|syntax|javascript|1529530|entityMap|0|LINK|mutability|MUTABLE|url^0|0|0|2G|0|0|0|0^^$0|@$1|2|3|4|5|6|7|T|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|U|8|@]|9|@$D|V|E|W|1|X]]|A|$]]|$1|F|3|G|5|6|7|Y|8|@]|9|@]|A|$]]|$1|H|3|I|5|J|7|Z|8|@]|9|@]|A|$K|L]]|$1|M|3|-4|5|6|7|10|8|@]|9|@]|A|$]]]|N|$O|$5|P|Q|R|A|$S|C]]]]

this is a generic base converter

<a href="http://dpatrickcaldwell.blogspot.com/2009/05/converting-decimal-to-hexadecimal-with.html" rel="noreferrer">http://dpatrickcaldwell.blogspot.com/2009/05/converting-decimal-to-hexadecimal-with.html</a>

you can do 

<pre><code>select reverse(dbo.ConvertToBase(5, 2)) -- 101
</code></pre>

blocks|key|1529610|text|实际上，使用普通的旧式SQL实现这一点非常简单。只需使用按位AND。让我有点惊讶的是，网上竟然没有一个简单的解决方案(没有包含UDF)。在我的例子中，我真的想检查bits是打开还是关闭(数据来自dotnet+eNums)。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1529611|相应地，这里是一个示例，它将给你单独和一起-位值和二进制字符串(大联合只是一种生成跨DB工作的数字的老套方法：|1529612|++++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))+
++++%2Bcast(+cast(t.Number+&+32+as+bit)+as+CHAR(1))
++++%2Bcast(+cast(t.Number+&+16+as+bit)++as+CHAR(1))
++++%2Bcast(+cast(t.Number+&+8+as+bit)++as+CHAR(1))
++++%2Bcast(+cast(t.Number+&+4+as+bit)++as+CHAR(1))
++++%2Bcast(+cast(t.Number+&+2+as+bit)+++as+CHAR(1))
++++%2Bcast(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))+
++++++++++++++++++++%2Bcast(+cast(t.Number+&+32+as+bit)+as+CHAR(1))
++++++++++++++++++++%2Bcast(+cast(t.Number+&+16+as+bit)++as+CHAR(1))
++++++++++++++++++++%2Bcast(+cast(t.Number+&+8+as+bit)++as+CHAR(1))
++++++++++++++++++++%2Bcast(+cast(t.Number+&+4+as+bit)++as+CHAR(1))
++++++++++++++++++++%2Bcast(+cast(t.Number+&+2+as+bit)+++as+CHAR(1))
++++++++++++++++++++%2Bcast(cast(t.Number+&+1+as+bit)++as+CHAR(1))
++++++++++++++++++++,
++++++++++++++++++++PATINDEX('%251%25',+cast(cast(t.Number+&+64+as+bit)+as+CHAR(1))+
++++++++++++++++++++++++++++++++++++++++%2Bcast(+cast(t.Number+&+32+as+bit)+as+CHAR(1))
++++++++++++++++++++++++++++++++++++++++%2Bcast(+cast(t.Number+&+16+as+bit)++as+CHAR(1))
++++++++++++++++++++++++++++++++++++++++%2Bcast(+cast(t.Number+&+8+as+bit)++as+CHAR(1))
++++++++++++++++++++++++++++++++++++++++%2Bcast(+cast(t.Number+&+4+as+bit)++as+CHAR(1))
++++++++++++++++++++++++++++++++++++++++%2Bcast(+cast(t.Number+&+2+as+bit)+++as+CHAR(1))
++++++++++++++++++++++++++++++++++++++++%2Bcast(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|code-block|syntax|javascript|1529613|产生以下结果：|1529614|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|1529615|entityMap^0|0|0|0|0|0^^$0|@$1|2|3|4|5|6|7|O|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|P|8|@]|9|@]|A|$]]|$1|D|3|E|5|F|7|Q|8|@]|9|@]|A|$G|H]]|$1|I|3|J|5|6|7|R|8|@]|9|@]|A|$]]|$1|K|3|L|5|F|7|S|8|@]|9|@]|A|$G|H]]|$1|M|3|-4|5|6|7|T|8|@]|9|@]|A|$]]]|N|$]]

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:

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

 , cast(cast(t.Number &amp; 64 as bit) as CHAR(1)) 
 +cast( cast(t.Number &amp; 32 as bit) as CHAR(1))
 +cast( cast(t.Number &amp; 16 as bit) as CHAR(1))
 +cast( cast(t.Number &amp; 8 as bit) as CHAR(1))
 +cast( cast(t.Number &amp; 4 as bit) as CHAR(1))
 +cast( cast(t.Number &amp; 2 as bit) as CHAR(1))
 +cast(cast(t.Number &amp; 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 &amp; 64 as bit) as CHAR(1)) 
 +cast( cast(t.Number &amp; 32 as bit) as CHAR(1))
 +cast( cast(t.Number &amp; 16 as bit) as CHAR(1))
 +cast( cast(t.Number &amp; 8 as bit) as CHAR(1))
 +cast( cast(t.Number &amp; 4 as bit) as CHAR(1))
 +cast( cast(t.Number &amp; 2 as bit) as CHAR(1))
 +cast(cast(t.Number &amp; 1 as bit) as CHAR(1))
 ,
 PATINDEX('%1%', cast(cast(t.Number &amp; 64 as bit) as CHAR(1)) 
 +cast( cast(t.Number &amp; 32 as bit) as CHAR(1))
 +cast( cast(t.Number &amp; 16 as bit) as CHAR(1))
 +cast( cast(t.Number &amp; 8 as bit) as CHAR(1))
 +cast( cast(t.Number &amp; 4 as bit) as CHAR(1))
 +cast( cast(t.Number &amp; 2 as bit) as CHAR(1))
 +cast(cast(t.Number &amp; 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
</code></pre>

Produces this result:

<pre><code>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
</code></pre>

blocks|key|1529635|text|这个怎么样..。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1529636|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;|code-block|syntax|javascript|1529637|entityMap^0|0|0^^$0|@$1|2|3|4|5|6|7|I|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|J|8|@]|9|@]|A|$E|F]]|$1|G|3|-4|5|6|7|K|8|@]|9|@]|A|$]]]|H|$]]

How about this...

<pre><code>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;
</code></pre>

blocks|key|1529721|text|我相信这种方法简化了很多其他人提出的想法。它使用逐位算术以及FOR+XML技巧和CTE来生成二进制数字。|type|unstyled|depth|inlineStyleRanges|offset|length|style|CODE|entityRanges|data|1529722|DECLARE+@my_int+INT+=+5

;WITH+CTE_Binary+AS
(
++++SELECT+1+AS+seq,+1+AS+val
++++UNION+ALL
++++SELECT+seq+%2B+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|code-block|syntax|javascript|1529723|entityMap^0|U|7|0|0^^$0|@$1|2|3|4|5|6|7|M|8|@$9|N|A|O|B|C]]|D|@]|E|$]]|$1|F|3|G|5|H|7|P|8|@]|D|@]|E|$I|J]]|$1|K|3|-4|5|6|7|Q|8|@]|D|@]|E|$]]]|L|$]]

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

<pre><code>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 &lt; 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 &amp; B2.val = B2.val
 ORDER BY
 B1.seq DESC
 FOR XML PATH('')
) AS val
</code></pre>

blocks|key|1529784|text|我使用下面的ITVF函数将十进制转换为二进制，因为它是一个内联函数，您不必“担心”优化器执行的多次读取。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1529785|++++++++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)+%25+2+
+++++FROM+Tally+t+
+++++WHERE+CONVERT(bigint,@Decimal)+>=+POWER(2,t.n)
+++++)


+++++SELECT+TwoBaseBinary+=++''+%2B
++++++++(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*/|code-block|syntax|javascript|1529786|entityMap^0|0|0^^$0|@$1|2|3|4|5|6|7|I|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|J|8|@]|9|@]|A|$E|F]]|$1|G|3|-4|5|6|7|K|8|@]|9|@]|A|$]]]|H|$]]

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.

<pre><code> 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) &gt;= 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 -&gt; 10011010010*/
</code></pre>

blocks|key|1529794|text|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++''+%7C%7C+t0.c+%7C%7C+t1.c+%7C%7C+t2.c+%7C%7C+t3.c+%7C%7C+t4.c+%7C%7C+t5.c+%7C%7C+t6.c+%7C%7C+t7.c+%7C%7C+t8.c+%7C%7C+t9.c+%7C%7C+ta.c+%7C%7C+tb.c+%7C%7C+tc.c+%7C%7C+td.c+%7C%7C+te.c+%7C%7C+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|type|code-block|depth|inlineStyleRanges|entityRanges|data|syntax|javascript|1529795|标准SQL+(在PostgreSQL中测试)。|unstyled|1529796|entityMap^0|0|0^^$0|@$1|2|3|4|5|6|7|I|8|@]|9|@]|A|$B|C]]|$1|D|3|E|5|F|7|J|8|@]|9|@]|A|$]]|$1|G|3|-4|5|F|7|K|8|@]|9|@]|A|$]]]|H|$]]

<pre><code>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
</code></pre>

Standard SQL (tested in PostgreSQL).

blocks|key|1744764|text|在SQL+Server上，您可以尝试类似以下示例的操作：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1744765|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++|code-block|syntax|javascript|1744766|entityMap^0|0|0^^$0|@$1|2|3|4|5|6|7|I|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|J|8|@]|9|@]|A|$E|F]]|$1|G|3|-4|5|6|7|K|8|@]|9|@]|A|$]]]|H|$]]

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

<pre><code>DECLARE @Int int = 321

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

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

blocks|key|1529926|text|这里是对accepted+answer+from+Sean的一点更改，因为我发现它的限制是只允许在输出中使用硬编码的位数。在我的日常使用中，我发现要么只获取最高的1位数，要么指定我希望返回的位数，这会更有用。它将自动用0填充侧面，这样它最多可以排到8位、16位或您想要的任何位数。|type|unstyled|depth|inlineStyleRanges|entityRanges|offset|length|data|1529927|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+%25+2)+%2B+@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))+%2B+@BinStr;
++++++++End;
++++End;

++++Return+@BinStr;

End;|code-block|syntax|javascript|1529928|entityMap|0|LINK|mutability|MUTABLE|url|/a/127371/5764553^0|4|P|0|0|0^^$0|@$1|2|3|4|5|6|7|Q|8|@]|9|@$A|R|B|S|1|T]]|C|$]]|$1|D|3|E|5|F|7|U|8|@]|9|@]|C|$G|H]]|$1|I|3|-4|5|6|7|V|8|@]|9|@]|C|$]]]|J|$K|$5|L|M|N|C|$O|P]]]]

Here's a bit of a change to the <a href="/a/127371/5764553">accepted answer from Sean</a>, 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.

<pre><code>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 &gt; 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 &lt;= 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;
</code></pre>

blocks|key|1744908|text|您可以使用递归CTE表来执行此操作。在这个示例代码中，它被设置为16位，但您可以通过更改16->来实现任意长度。此外，要转换的数据是表DecimalTable|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1744909|WITH+DecimalTable+AS+(SELECT+10+decimal_num+UNION+SELECT+20),
+++++DtoB+AS+(SELECT+decimal_num
+++++++++++++++++++++,1+n
+++++++++++++++++++++,CAST(CAST(decimal_num%252+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|code-block|syntax|javascript|1744910|entityMap^0|0|0^^$0|@$1|2|3|4|5|6|7|I|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|J|8|@]|9|@]|A|$E|F]]|$1|G|3|-4|5|6|7|K|8|@]|9|@]|A|$]]]|H|$]]

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

<pre><code>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&amp;n as bit), binary_num)
 AS VARCHAR(16)) binary_num
 FROM DtoB
 WHERE n&lt;POWER(2,16))

 SELECT decimal_num, binary_num
 FROM DtoB
</code></pre>

blocks|key|1744930|text|为什么不干脆..。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1744931|declare+@num+int+=+75

select
@num+[Dec]
,+convert+(varchar(1),+@num+/+128+%25+2)
%2B+convert+(varchar(1),+@num+/+64+%25+2)
%2B+convert+(varchar(1),+@num+/+32+%25+2)
%2B+convert+(varchar(1),+@num+/+16+%25+2)
%2B+convert+(varchar(1),+@num+/+8+%25+2)
%2B+convert+(varchar(1),+@num+/+4+%25+2)
%2B+convert+(varchar(1),+@num+/+2+%25+2)
%2B+convert+(varchar(1),+@num+%25+2)+as+[Bin]|code-block|syntax|javascript|1744932|entityMap^0|0|0^^$0|@$1|2|3|4|5|6|7|I|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|J|8|@]|9|@]|A|$E|F]]|$1|G|3|-4|5|6|7|K|8|@]|9|@]|A|$]]]|H|$]]

Why not simply...
<pre><code>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]
</code></pre>

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.

SQL Server Convert integer to binary string

翻译质量差，导致语言生硬或混乱。

没有提供实际的解决方法或示例。

解答不清晰，无法理解或解决问题。

页面排版不美观，阅读体验差。

文章

问答

视频

学习中心

腾讯云实验室

直播

竞赛

腾讯云代码分析专区

腾讯iOA零信任安全管理系统专区

腾讯云架构师技术同盟交流圈

腾讯云数据库专区

腾讯云顾问专区

腾讯云原生专区

腾讯混元专区

腾讯云TCE专区

腾讯云Lighthouse专区

腾讯云HAI专区

腾讯云Edgeone专区

腾讯云存储专区

腾讯云智能专区

腾讯轻联专区 

腾讯云开发专区

TAPD专区

腾讯轻量云游戏服专区

腾讯云最具价值专家

腾讯云架构师技术同盟

腾讯云创作之星

腾讯云开发者先锋

腾讯云代码助手

云原生构建

TAPD 敏捷项目管理

Cloud Studio

SDK中心

API中心

命令行工具

涵盖代码开发、场景应用、自动测试全流程，助你从零构建专属AI助手

一站式MCP教程库，解锁AI应用新玩法

我想知道在SQL中是否有一种简单的方法可以将整数转换为它的二进制表示形式，然后将其存储为varchar。例如，5将被转换为"101“并存储为varchar。

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

社区

活动

圈层

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

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

社区

活动

圈层

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

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