Discovering the binary value in SQL Server

Navigation
3

Is there a function in SQL Server that converts an integer to binary? Example: 

SELECT Funcao_Binario(2) -> Saida : 0010
SELECT Funcao_Binario(4) -> Saída : 0100
SELECT Funcao_Binario(5) -> Saída : 0101
    
asked by anonymous 20.10.2016 / 19:58

2 answers

6

You can create a function for this: 

CREATE FUNCTION [dbo].[DecimalToBinary]
(
    @Input bigint
)
RETURNS varchar(255)
AS
BEGIN

    DECLARE @Output varchar(255) = ''

    WHILE @Input > 0 BEGIN

        SET @Output = @Output + CAST((@Input % 2) AS varchar)
        SET @Input = @Input / 2

    END

    RETURN REVERSE(@Output)

END

And use it this way: 

SELECT dbo.DecimalToBinary(2);
SELECT dbo.DecimalToBinary(4);
SELECT dbo.DecimalToBinary(5);

Source:

  

link

    
20.10.2016 / 20:23
2

Using bitwise operations: 

CREATE FUNCTION dbo.Int2Binary (@i INT) RETURNS NVARCHAR(16) AS BEGIN
    RETURN
        CASE WHEN CONVERT(VARCHAR(16), @i & 32768 ) > 0 THEN '1' ELSE '0'   END +
        CASE WHEN CONVERT(VARCHAR(16), @i & 16384 ) > 0 THEN '1' ELSE '0'   END +
        CASE WHEN CONVERT(VARCHAR(16), @i &  8192 ) > 0 THEN '1' ELSE '0'   END +
        CASE WHEN CONVERT(VARCHAR(16), @i &  4096 ) > 0 THEN '1' ELSE '0'   END +
        CASE WHEN CONVERT(VARCHAR(16), @i &  2048 ) > 0 THEN '1' ELSE '0'   END +
        CASE WHEN CONVERT(VARCHAR(16), @i &  1024 ) > 0 THEN '1' ELSE '0'   END +
        CASE WHEN CONVERT(VARCHAR(16), @i &   512 ) > 0 THEN '1' ELSE '0'   END +
        CASE WHEN CONVERT(VARCHAR(16), @i &   256 ) > 0 THEN '1' ELSE '0'   END +
        CASE WHEN CONVERT(VARCHAR(16), @i &   128 ) > 0 THEN '1' ELSE '0'   END +
        CASE WHEN CONVERT(VARCHAR(16), @i &    64 ) > 0 THEN '1' ELSE '0'   END +
        CASE WHEN CONVERT(VARCHAR(16), @i &    32 ) > 0 THEN '1' ELSE '0'   END +
        CASE WHEN CONVERT(VARCHAR(16), @i &    16 ) > 0 THEN '1' ELSE '0'   END +
        CASE WHEN CONVERT(VARCHAR(16), @i &     8 ) > 0 THEN '1' ELSE '0'   END +
        CASE WHEN CONVERT(VARCHAR(16), @i &     4 ) > 0 THEN '1' ELSE '0'   END +
        CASE WHEN CONVERT(VARCHAR(16), @i &     2 ) > 0 THEN '1' ELSE '0'   END +
        CASE WHEN CONVERT(VARCHAR(16), @i &     1 ) > 0 THEN '1' ELSE '0'   END
END;
GO

Usage: 

SELECT dbo.Int2Binary(2)
SELECT dbo.Int2Binary(50)

The largest integer that can be converted is 65536, since varchar (16) is being used in the above function.

Source: link

    
21.10.2016 / 08:34
Remove comma out of brackets - Regex Count the number of times a character appears in a string