SQL Server Converter inteiro em cadeia binária

Eu queria saber se havia uma maneira fácil no SQL para converter um inteiro para sua representação binária e, em seguida, armazená-lo como um varchar.

Por exemplo, 5 seria convertido para “101” e armazenado como um varchar.

A seguir pode ser codificado em uma function. Você precisaria compensar os zeros iniciais para atender aos requisitos da sua pergunta.

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 

Na verdade, isso é realmente simples usando SQL antigo simples. Apenas use ANDs bit a bit. Fiquei um pouco surpreso por não haver uma solução simples publicada on-line (que não envolvia UDFs). No meu caso, eu realmente queria verificar se os bits estavam ativos ou não (os dados são provenientes de eNums dotnet).

Por conseguinte, aqui está um exemplo que lhe dará separadamente e juntos – valores de bits e string binária (a grande união é apenas uma maneira hacky de produzir números que funcionará em 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 

Produz esse resultado:

 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 

este é um conversor de base genérico

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

você pode fazer

 select reverse(dbo.ConvertToBase(5, 2)) -- 101 
 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 

Por favor, veja este post, convertendo inteiros para seqüências binárias , eu postei um tempo atrás.

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

Aqui está uma pequena mudança na resposta aceita por Sean , já que achei limitante permitir apenas um número de dígitos codificados na saída. No meu uso diário, acho mais útil obter apenas o dígito mais alto, ou especificar quantos dígitos espero. Ele irá automaticamente preencher o lado com 0s, para que ele seja alinhado até 8, 16 ou qualquer número de bits que você queira.

 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; 

Que tal agora…

 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; 

Acredito que esse método simplifica muitas outras ideias que outros apresentaram. Ele usa aritmética bitwise junto com o truque FOR XML com um CTE para gerar os dígitos binários.

 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 

Eu usei a seguinte function ITVF para converter de decimal para binário, pois é uma function embutida que você não precisa se preocupar com várias leituras realizadas pelo otimizador.

  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 tN , CONVERT(BIGINT, @Decimal) / POWER(2,TN) , CONVERT(BIGINT, @Decimal) / POWER(2,TN) % 2 FROM Tally t WHERE CONVERT(bigint,@Decimal) >= POWER(2,tn) ) 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*/ 
 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 padrão (testado no PostgreSQL).

No SQL Server, você pode tentar algo como o exemplo abaixo:

 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