You are here:

Blog Archive:

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

Search in blogs

 KeywordsPhrase

Blog Categories:

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

Blog Tags:

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

TNWikiSummit

 Read this before you use the blog!

T-SQL: Converting between Decimal, Binary, and Hexadecimal

דצמ14

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

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

Categories: SQL