en-UShe-IL
You are here:   Blog
Register   |  Login

Blog Archive:

Maximize
* Can be used in order to search for older blogs Entries

Search in blogs


Blog Categories:

Maximize
* Can be used in order to search for blogs Entries by Categories

Blog Tags:

Maximize
* Can be used in order to search for blogs by keywords

TNWikiSummit


Awared MVP

 


Microsoft® Community Contributor 


Microsoft® Community Contributor


 Read first, before you you use the blog! Maximize

Recent Entries

Minimize
דצמ14

Written by: ronen ariely
14/12/2015 10:38 RssIcon

I see in the forum this question raised all the time, and yet the solution that people get usually based on looping and complex math, while there is a simple and much better solution as shown in this code. In this short blog I will wrote a simple and VERY fast solution to convert Decimal number to Binary number, Decimal number to Hexadecimal number, and vise versa without using any loop. My solution for binary base is based on using BITWISE.

* The advantage of using BITWISE is that the data comparison is done in binary level and without mathematical calculation.

/********* Decimal to Hexadecimal */
declare @i INT = 10
SELECT CONVERT(VARBINARY(16),@i)
GO
 
/********* Hexadecimal to Decimal */
declare @i VARBINARY(16) = 0x0000000A
SELECT CAST(@i as INT)
GO
 
/********* Decimal to binary */
-- MAX VALUE: 65535 which is binary 1111111111111111
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
SELECT dbo.Int2Binary(2)
GO
 
/********* binary to Decimal */
CREATE FUNCTION dbo.Binary2Int (@i VARCHAR(16)) RETURNS INT AS BEGIN
    SET @i = RIGHT( REPLICATE('0',16) + @i, 16)
    RETURN
        SUBSTRING(@i, 1,1) * 32768 +
        SUBSTRING(@i, 2,1) * 16384 +
        SUBSTRING(@i, 3,1) *  8192 +
        SUBSTRING(@i, 4,1) *  4096 +
        SUBSTRING(@i, 5,1) *  2048 +
        SUBSTRING(@i, 6,1) *  1024 +
        SUBSTRING(@i, 7,1) *   512 +
        SUBSTRING(@i, 8,1) *   256 +
        SUBSTRING(@i, 9,1) *   128 +
        SUBSTRING(@i,10,1) *    64 +
        SUBSTRING(@i,11,1) *    32 +
        SUBSTRING(@i,12,1) *    16 +
        SUBSTRING(@i,13,1) *     8 +
        SUBSTRING(@i,14,1) *     4 +
        SUBSTRING(@i,15,1) *     2 +
        SUBSTRING(@i,16,1) *     1
END;
GO
 
DECLARE @i INT = 65535 -- Max value since we use varchar(16) for the binary
SELECT @i, dbo.Int2Binary(@i), dbo.Binary2Int(  dbo.Int2Binary(@i)   )
GO

 
Have fun :-)