fecha - convert varchar to numeric sql

SQL Server Convertir entero a cadena binaria

Question

Me preguntaba si había una manera fácil en SQL de convertir un número entero a su representación binaria y luego almacenarlo como varchar.

Por ejemplo, 5 se convertiría a "101" y se almacenaría como varchar.

Answer 1

Lo siguiente podría codificarse en una función. Debería recortar los ceros a la izquierda para cumplir los requisitos de su pregunta.

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

Answer 2

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

Answer 3

Por favor, mira esta publicación en el blog, Converting Integers to Binary Strings , que publiqué hace un tiempo.

Answer 4

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

Answer 5

este es un convertidor base genérico

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

tu puedes hacer

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

Answer 6

Qué tal esto...

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;

Answer 7

Creo que este método simplifica muchas de las otras ideas que otros han presentado. Utiliza la aritmética bit a bit junto con el truco FOR XML con un CTE para generar los dígitos binarios.

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

Answer 8

Utilicé la siguiente función de ITVF para convertir de decimal a binario ya que es una función en línea que no necesita "preocuparse" por lecturas múltiples realizadas por el optimizador.

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

Answer 9

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 estándar (probado en PostgreSQL).

Answer 10

En SQL Server, puedes probar algo como el siguiente ejemplo:

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 

Answer 11

Aquí hay un pequeño cambio en la respuesta aceptada de Sean , ya que me pareció limitado limitarme a permitir un número codificado de dígitos en la salida. En mi uso diario, me resulta más útil obtener solo hasta el 1 dígito más alto o especificar cuántos dígitos estoy esperando. Automáticamente rellenará el lado con 0s, de modo que se alinee a 8, 16 o cualquier cantidad de bits que desee.

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;

Answer 12

En realidad, esto es REALMENTE SIMPLE usando SQL simple. Solo use ANDs bit a bit. Me sorprendió un poco que no haya una solución simple publicada en línea (que no haya invocado UDF). En mi caso, realmente quería verificar si los bits estaban activados o desactivados (los datos provienen de dotnet eNums).

De acuerdo con esto, aquí hay un ejemplo que le dará por separado y en conjunto: valores de bit y cadena binaria (la gran unión es solo una forma estrafalaria de producir números que funcionarán a través de 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

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