Convert integer to hex and hex to integer

Question

So I have this query working  where signal data is a column  in Sybase but it doesn t work in Microsoft SQL Server   HEXTOINT SUBSTRING  INTTOHEX signal data   5 2   as Signal   I also have it in Excel  where A1 contains the value     HEX2DEC LEFT DEC2HEX A1  LEN DEC2HEX A1  -2     Does anyone know how I would do this in SQL Server

User · Answer

The traditonal 4 bit hex is pretty direct. Hex String to Integer (Assuming value is stored in field called FHexString) :

CONVERT(BIGINT,CONVERT(varbinary(4),
                (SELECT master.dbo.fn_cdc_hexstrtobin(

                    LEFT(FMEID_ESN,8)                       
                ))
                ))

Integer to Hex String (Assuming value is stored in field called FInteger):

(SELECT master.dbo.fn_varbintohexstr(CONVERT(varbinary,CONVERT(int,
                    FInteger
                ))))

Important to note is that when you begin to use bit sizes that cause register sharing, especially on an intel machine, your High and Low and Left and Rights in the registers will be swapped due to the little endian nature of Intel. For example, when using a varbinary(3), we're talking about a 6 character Hex. In this case, your bits are paired as the following indexes from right to left "54,32,10". In an intel system, you would expect "76,54,32,10". Since you are only using 6 of the 8, you need to remember to do the swaps yourself. "76,54" will qualify as your left and "32,10" will qualify as your right. The comma separates your high and low. Intel swaps the high and lows, then the left and rights. So to do a conversion...sigh, you got to swap them yourselves for example, the following converts the first 6 of an 8 character hex:

(SELECT master.dbo.fn_replvarbintoint(
                CONVERT(varbinary(3),(SELECT master.dbo.fn_cdc_hexstrtobin(
                    --intel processors, registers are switched, so reverse them 


                    ----second half
                    RIGHT(FHex8,2)+ --0,1 (0 indexed)
                    LEFT(RIGHT(FHex8,4),2)+ -- 2,3 (oindex)
                    --first half
                    LEFT(RIGHT(FHex8,6),2) --4,5

                )))
                ))

It's a bit complicated, so I would try to keep my conversions to 8 character hex's (varbinary(4)).

In summary, this should answer your question. Comprehensively.

User · Answer

Actually  the built-in function is named master dbo fn varbintohexstr   So  for example   SELECT 100  master dbo fn varbintohexstr 100    Gives you  100         0x00000064

User · Answer

SQL Server equivalents to Excel s string-based DEC2HEX  HEX2DEC functions   --Convert INT to hex string  PRINT CONVERT VARCHAR 8  CONVERT VARBINARY 4   16777215  2  --DEC2HEX  --Convert hex string to INT  PRINT CONVERT INT CONVERT VARBINARY 4   00FFFFFF  2   --HEX2DEC

User · Answer

Maksym Kozlenko has a nice solution  and others come close to unlocking it s full potential but then miss completely to realized that you can define any sequence of characters  and use it s length as the Base   Which is why I like this slightly modified version of his solution  because it can work for base 16  or base 17  and etc   For example  what if you wanted letters and numbers  but don t like I s for looking like 1 s and O s for looking like 0 s   You can define any sequence this way  Below is a form of a  Base 36  that skips the I and O to create a  modified base 34    Un-comment the hex line instead to run as hex   declare  value int   1234567890  DECLARE  seq varchar 100     0123456789ABCDEFGHJKLMNPQRSTUVWXYZ  -- modified base 34 --DECLARE  seq varchar 100     0123456789ABCDEF  -- hex DECLARE  result varchar 50  DECLARE  digit char 1  DECLARE  baseSize int   len  seq  DECLARE  workingValue int    value  SET  result   SUBSTRING  seq    workingValue  baseSize  1  1   WHILE  workingValue  gt  0 BEGIN     SET  digit   SUBSTRING  seq     workingValue  baseSize   baseSize  1  1       SET  workingValue    workingValue  baseSize     IF  workingValue  lt  gt  0 SET  result    digit    result END   select  value as Value   baseSize as BaseSize   result as Result      Value         BaseSize    Result      1234567890    34          T5URAA   I also moved value over to a working value  and then work from the working value copy  as a personal preference   Below is additional for reversing the transformation  for any sequence  with the base defined as the length of the sequence   declare  value varchar 50     T5URAA   DECLARE  seq varchar 100     0123456789ABCDEFGHJKLMNPQRSTUVWXYZ  -- modified base 34 --DECLARE  seq varchar 100     0123456789ABCDEF  -- hex DECLARE  result int   0 DECLARE  digit char 1  DECLARE  baseSize int   len  seq  DECLARE  workingValue varchar 50     value  DECLARE  PositionMultiplier int   1 DECLARE  digitPositionInSequence int   0  WHILE len  workingValue   gt  0 BEGIN     SET  digit   right  workingValue 1      SET  digitPositionInSequence   CHARINDEX  digit  seq      SET  result    result       digitPositionInSequence -1     PositionMultiplier       --select  digit   digitPositionInSequence   PositionMultiplier   result      SET  workingValue   left  workingValue len  workingValue -1      SET  PositionMultiplier    PositionMultiplier    baseSize END   select  value as Value   baseSize as BaseSize   result as Result

User · Answer

Convert int to hex    SELECT FORMAT 512 255  X

User · Answer

The answer by Maksym Kozlenko is nice and can be slightly modified to handle encoding a numeric value to any code format  For example   CREATE FUNCTION  dbo   IntToAlpha   Value int  RETURNS varchar 30  AS BEGIN     DECLARE  CodeChars varchar 100       SET  CodeChars    ABCDEFGHIJKLMNOPQRSTUVWXYZ      DECLARE  CodeLength int   26     DECLARE  Result varchar 30           DECLARE  Digit char 1       SET  Result   SUBSTRING  CodeChars    Value    CodeLength    1  1      WHILE  Value  gt  0     BEGIN         SET  Digit   SUBSTRING  CodeChars     Value    CodeLength     CodeLength    1  1          SET  Value    Value    CodeLength         IF  Value  lt  gt  0 SET  Result    Digit    Result     END       RETURN  Result END   So  a big number like 150 million  becomes only 6 characters  150 000 000    MQGJMU    You could also use different characters in different sequences as an encrypting device  Or pass in the code characters and length of characters and use as a salting method for encrypting   And the reverse   CREATE FUNCTION  dbo   AlphaToInt   Value varchar 7   RETURNS int AS BEGIN     DECLARE  CodeChars varchar 100       SET  CodeChars    ABCDEFGHIJKLMNOPQRSTUVWXYZ      DECLARE  CodeLength int   26     DECLARE  Digit char 1      DECLARE  Result int   0     DECLARE  DigitValue int     DECLARE  Index int   0     DECLARE  Reverse varchar 7      SET  Reverse   REVERSE  Value       WHILE  Index  lt  LEN  Value      BEGIN         SET  Digit   SUBSTRING  Reverse   Index   1  1          SET  DigitValue    CHARINDEX  Digit   CodeChars  - 1    POWER  CodeLength   Index          SET  Result    Result    DigitValue         SET  Index    Index   1     END      RETURN  Result

User · Answer

IIF Fields HIGHLIGHT COLOUR Value      FFFFFF       amp  hex Fields HIGHLIGHT COLOUR Value   amp  StrDup 6-LEN hex Fields HIGHLIGHT COLOUR Value    0      Is working for me as an expression in font colour

User · Answer

Declare  Dato xml Set  Dato   Convert xml    lt dato gt FF lt  dato gt    Select Cast  rw value   xs hexBinary  text   1       varbinary max     as int   From  Dato nodes  dato   as T rw

User · Answer

Here is the function for SQL server which converts integer value into its hexadecimal representation as a varchar  It should be easy to adapt to other database types   For example   SELECT dbo ToHex 4095  -- gt  FFF   SQL   CREATE FUNCTION ToHex  value int  RETURNS varchar 50  AS BEGIN     DECLARE  seq char 16      DECLARE  result varchar 50      DECLARE  digit char 1      SET  seq    0123456789ABCDEF       SET  result   SUBSTRING  seq    value 16  1  1       WHILE  value  gt  0     BEGIN         SET  digit   SUBSTRING  seq     value 16  16  1  1           SET  value    value 16         IF  value  lt  gt  0 SET  result    digit    result     END       RETURN  result END GO

User · Answer

Convert INT to hex   SELECT CONVERT VARBINARY 8   16777215    Convert hex to INT   SELECT CONVERT INT  0xFFFFFF    Update 2015-03-16  The above example has the limitation that it only works when the HEX value is given as an integer literal  For completeness  if the value to convert is a hexadecimal string  such as found in a varchar column  use   -- If the  0x  marker is present  SELECT CONVERT INT  CONVERT VARBINARY   0x1FFFFF   1    -- If the  0x  marker is NOT present  SELECT CONVERT INT  CONVERT VARBINARY   1FFFFF   2     Note  The string must contain an even number of hex digits  An odd number of digits will yield an error   More details can be found in the  Binary Styles  section of CAST and CONVERT  Transact-SQL   I believe SQL Server 2008 or later is required

User · Answer

Use master dbo fnbintohexstr 16777215  to convert to a varchar representation

User · Answer

Below are two functions  dbo HexToInt and dbo IntToHex  I use them for such conversion   if OBJECT ID  dbo HexToInt   is not null     drop function dbo HexToInt GO create function dbo HexToInt   chars varchar max   returns int begin     declare  char varchar 1    len int   i int   r int   tmp int   pow int     set  chars   RTRIM LTRIM  chars       set  len   LEN  chars      set  i   1     set  r   0     while  i  lt    len     begin         set  pow    len -  i         set  char   SUBSTRING  chars   i  1          if  char    0              set  tmp   0         else if  char    1              set  tmp   1         else if  char    2              set  tmp   2         else if  char    3              set  tmp   3         else if  char    4              set  tmp   4         else if  char    5              set  tmp   5         else if  char    6              set  tmp   6         else if  char    7              set  tmp   7         else if  char    8              set  tmp   8         else if  char    9              set  tmp   9         else if  char    A              set  tmp   10         else if  char    B              set  tmp   11         else if  char    C              set  tmp   12         else if  char    D              set  tmp   13         else if  char    E              set  tmp   14         else if  char    F              set  tmp   15         set  r    r    tmp   POWER 16  pow          set  i    i   1          end     return  r end   And the second one   if OBJECT ID  dbo IntToHex   is not null     drop function dbo IntToHex GO create function dbo IntToHex   val int  returns varchar max  begin     declare  r varchar max    tmp int   v1 int   v2 int   char varchar 1      set  tmp    val     set  r          while 1 1     begin         set  v1    tmp   16         set  v2    tmp   16         if  v2   0             set  char    0          else if  v2   1             set  char    1          else if  v2   2             set  char    2          else if  v2   3             set  char    3          else if  v2   4             set  char    4          else if  v2   5             set  char    5          else if  v2   6             set  char    6          else if  v2   7             set  char    7          else if  v2   8             set  char    8          else if  v2   9             set  char    9          else if  v2   10             set  char    A          else if  v2   11             set  char    B          else if  v2   12             set  char    C          else if  v2   13             set  char    D          else if  v2   14             set  char    E          else if  v2   15             set  char    F          set  tmp    v1          set  r    char    r         if  tmp   0             break     end     return  r end

User · Answer

To convert Hex strings to INT  I have used this in the past   It can be modified to convert any base to INT in fact  Octal  Binary  whatever   Declare  Str varchar 200  Set  str    F000BE1A   Declare  ndx int Set  ndx   Len  str  Declare  RunningTotal  BigInt Set  RunningTotal   0  While  ndx  gt  0 Begin     Declare  Exponent BigInt     Set  Exponent   Len  Str  -  ndx      Set  RunningTotal    RunningTotal         Power 16   1 0   Exponent        Case Substring  str   ndx  1          When  0  then 0         When  1  then 1         When  2  then 2          When  3  then 3         When  4  then 4         When  5  then 5         When  6  then 6         When  7  then 7         When  8  then 8         When  9  then 9         When  A  then 10         When  B  then 11         When  C  then 12         When  D  then 13         When  E  then 14         When  F  then 15     End     Set  ndx    ndx - 1 End  Print  RunningTotal

User · Answer

It is possible using the function FORMAT available on SQL Server 2012 and above   select FORMAT 10  x2     Results in   0a

User · Answer

Given   declare  hexStr varchar 16    intVal int   IntToHexStr   select  hexStr   convert varbinary   intVal  1    HexStrToInt   declare      query varchar 100        parameters varchar 50   select      query    select  result   convert int      hb             parameters     result int output   exec master dbo Sp executesql  query   parameters   intVal output

[sql] Convert integer to hex and hex to integer

Examples related to sql

Examples related to sql-server

Examples related to integer

Examples related to hex