SQL Server 将整数转换为二进制字符串
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/127116/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
SQL Server Convert integer to binary string
提问by Ross Goddard
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.
我想知道 SQL 中是否有一种简单的方法可以将整数转换为其二进制表示形式，然后将其存储为 varchar。
For example 5 would be converted to "101" and stored as a varchar.
例如，5 将转换为“101”并存储为 varchar。
采纳答案by Sean
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
回答by Mathew Frank
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).
实际上，使用普通的旧 SQL 非常简单。只需使用按位与。我有点惊讶网上没有发布一个简单的解决方案（没有涉及 UDF）。就我而言，我真的很想检查位是打开还是关闭（数据来自 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:
因此，这里是一个示例，它将单独和一起为您提供 - 位值和二进制字符串（大联合只是一种生成可跨数据库工作的数字的hacky方式：
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
回答by Milox
this is a generic base converter
这是一个通用的基本转换器
http://dpatrickcaldwell.blogspot.com/2009/05/converting-decimal-to-hexadecimal-with.html
http://dpatrickcaldwell.blogspot.com/2009/05/converting-decimal-to-hexadecimal-with.html
you can do
你可以做
select reverse(dbo.ConvertToBase(5, 2)) -- 101
回答by Bohden M
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.
这是对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;
回答by hkravitz
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.
我使用以下 ITVF 函数将十进制转换为二进制，因为它是一个内联函数，您无需“担心”优化器执行的多次读取。
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*/
回答by Constantin
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
回答by Constantin
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);
回答by Tom H
I believe that this method simplifies a lot of the other ideas that others have presented. It uses bitwise arithmetic along with the FOR XMLtrick with a CTE to generate the binary digits.
我相信这种方法简化了其他人提出的许多其他想法。它使用按位算术以及带有FOR XMLCTE的技巧来生成二进制数字。
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
回答by user6453285
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).
标准 SQL（在 PostgreSQL 中测试）。
回答by Evandro
On SQL Server, you can try something like the sample below:
在 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
