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