How to convert integer to hex notation?

Question

Using: Firebird 2.5.3

In a stored procedure (PSQL), converting a number from hex notation to decimal notation is done easily:

DECLARE VARIABLE I INTEGER;

BEGIN

  I = CAST('0x0FFFE' AS INTEGER);  -- I will have the value 65534

How can the reverse be achieved? ie. Convert from decimal notation to hex notation?

Answer 1

Short of using a UDF (which would mean using an external library file), the solution is to write a stored procedure to accomplish this:

SET TERM ^^ ;
CREATE PROCEDURE INTTOHEX (
  INPUTNUMBER BigInt)
 returns (
  OUTPUTNUMBER VarChar(8))
AS
DECLARE VARIABLE Q BigInt;
DECLARE VARIABLE R BigInt;
DECLARE VARIABLE T BigInt;
DECLARE VARIABLE H VARCHAR(1);
DECLARE VARIABLE S VARCHAR(6);

begin
  /* Max input value allowed is: 4294967295 */

  S = 'ABCDEF';

  Q = 1;   
  OUTPUTNUMBER = '';
  T = INPUTNUMBER;
  WHILE (Q <> 0) DO
  BEGIN        

    Q = T / 16;
    R = MOD(T, 16);
    T = Q;

    IF (R > 9) THEN
     H = SUBSTRING(S FROM (R-9) FOR 1); 
    ELSE
     H = R;                                

    OUTPUTNUMBER = H || OUTPUTNUMBER ;
  END


  SUSPEND;
end ^^
SET TERM ; ^^

You can call this stored procedure from standard SQL or another stored procedure like this:

For example:

SELECT OUTPUTNUMBER FROM INTTOHEX(65534);