SQL Server Convertit un entier en string binary

Suivant pourrait être codé dans une fonction. Vous devez supprimer les zéros en tête pour répondre aux exigences de votre 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 

En fait, c'est vraiment simple en utilisant le vieux SQL. Utilisez simplement des ANDs au niveau du bit. J'étais un peu étonné qu'il n'y ait pas une solution simple mise en ligne (qui n'a pas invoqué UDFs). Dans mon cas, je voulais vraiment vérifier si les bits étaient allumés ou éteints (datatables proviennent de dotnet eNums).

En conséquence, voici un exemple qui vous donnera séparément et set des valeurs binarys et une string binary (le grand syndicat est juste une manière hacky de produire des nombres qui fonctionneront sur les bases de données:

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

Produit ce résultat:

 num bit7 bit6 bit5 bit4 bit3 bit2 bit1 binary_ssortingng binary_ssortingng_sortingmmed 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 

c'est un convertisseur de base générique

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

tu peux faire

 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 

S'il vous plaît voir cet article de blog, Conversion d'entiers en strings binarys , j'ai posté il y a un certain time.

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

Voici un peu de changement à la réponse acceptée de Sean , puisque je l'ai trouvé limitant à autoriser seulement un nombre de numbers codés en dur dans la sortie. Dans mon utilisation quotidienne, je trouve qu'il est plus utile d'avoir seulement le chiffre 1 le plus élevé, ou de spécifier le nombre de numbers que j'attends. Il tapera automatiquement le côté avec 0, de sorte qu'il aligne jusqu'à 8, 16, ou n'importe quel nombre de bits que vous voulez.

 Create function f_DecimalToBinarySsortingng ( @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 sortingm the output to match the number of digits specified. If (@MaxLength is not null) Begin If @MaxLength <= Len(@BinStr) Begin -- Trim down Set @BinStr = SubSsortingng(@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 dis-tu de ça…

 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; 

Je crois que cette méthode simplifie beaucoup d'autres idées que d'autres ont présentées. Il utilise l'arithmétique au niveau du bit avec le tour FOR XML avec un CTE pour générer les numbers binarys.

 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 

J'ai utilisé la fonction ITVF suivante pour convertir de décimal en binary car c'est une fonction en ligne que vous n'avez pas besoin de "s'inquiéter" de plusieurs lectures effectuées par l'optimiseur.

  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 standard (testé dans PostgreSQL).

Sur SQL Server, vous pouvez essayer quelque chose comme l'exemple ci-dessous:

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