Discovering the binary value in SQL Server

Asked

Viewed 313 times

3

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

SELECT Funcao_Binario(2) -> Saida : 0010
SELECT Funcao_Binario(4) -> Saída : 0100
SELECT Funcao_Binario(5) -> Saída : 0101
sql sql-server binary

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 this way:

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

Source:

http://improve.dk/converting-between-base-2-10-and-16-in-t-sql/

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

Use:

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

The largest integer convertible is 65536, since varchar(16) is being used in the above function.

Source: http://ariely.info/Blog/tabid/83/EntryId/169/T-SQL-Converting-between-Decimal-Binary-and-Hexadecimal.aspx

Browser other questions tagged sql sql-server binary

You are not signed in. Login or sign up in order to post.