devxlogo

T-SQL Data Conversion

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 Binaryfrom mytablemyint  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-admin

Share the Post: