T-SQL Data Conversion

Question:
I want to convert a signed integer into a 16-bit binary as part of a SELECT statement. For example:

select myint, howeveritsdone as Binary
from mytable

myint  Binary
----   ----------------
-137   1111111101110111
-100   1111111110011100

How can I do this?

Answer:
The kernel of the solution is that 16 is 2 to the fourth power. Each position in the hex string can be translated into four binary digits to create the final result. To do this, create a lookup table of the following structure and data:

 
 cHex   cBin 
 0      0000 
 1      0001 
 2      0010 
 3      0011 
 4      0100 
 5      0101 
 6      0110 
 7      0111 
 8      1000 
 9      1001 
 A      1010 
 B      1011 
 C      1100 
 D      1101 
 E      1110 
 F      1111

Take the hex result that comes from the binary keyword and decode it using the lookup table, stacking your result with the least-significant bit on the right (backwards).

(Thanks to David Cressey for help with this problem.)

DevX Pro

Submit a Tip

Browse "Security" Tips

Browse All Tips

0 Comments (click to add your comment)

Comment and Contribute