SQL PL function to convert int to hex

If you are converting from Sybase, you might run into issues for converting INT to HEX.

In Sybase:

1> select inttohex(234)
2> go

--------
000000EA

(1 row affected)

In DB2:

DB2 => values hex(234)

1
--------
EA000000

If you want the same exact behavior from DB2, you could write a function that will do same as Sybase.

CREATE FUNCTION INT2HEX(N INTEGER)
RETURNS VARCHAR(100)
LANGUAGE SQL
CONTAINS SQL
BEGIN ATOMIC
   DECLARE RESULT VARCHAR(1000) DEFAULT '';
   DECLARE IDX INTEGER DEFAULT 0;
   SET IDX = N;
   WHILE (IDX > 0) DO
      SET RESULT = SUBSTR('0123456789ABCDEF',(MOD(IDX,16)+1),1)||RESULT;
      SET IDX = IDX / 16;
   END WHILE;
   RETURN CHAR(RIGHT(REPEAT('0',8)||RESULT,8),8);
END
!

Run int2hex function in DB2

db2 => values int2hex(234)

1
----------------------------
000000EA

  1 record(s) selected.

HEXTORAW in DB2

The output is a binary format and not string as you might think.

CREATE FUNCTION HEXTORAW( INSTR VARCHAR(32672) )
     RETURNS VARCHAR(16336) FOR BIT DATA
     DETERMINISTIC EXTERNAL ACTION CONTAINS SQL
   BEGIN ATOMIC
     DECLARE RETURN_STR, TEMP_STR VARCHAR(16336) DEFAULT '';
     DECLARE LEN INT;
     IF INSTR IS NULL THEN
       RETURN NULL;
     END IF;
     SET (TEMP_STR, LEN) = (INSTR, LENGTH(INSTR));
     IF MOD( LEN,  2 ) = 1 THEN
       RETURN RAISE_ERROR('22018', 'HEXTORAW');
     END IF;
     WHILE LEN > 0 DO
       SET (RETURN_STR, TEMP_STR, LEN) 
         = (RETURN_STR ||  CHR((LOCATE(UCASE(SUBSTR(TEMP_STR,1,1)),'0123456789ABCDEF')-1) *16
                             + (LOCATE(UCASE(SUBSTR(TEMP_STR,2,1)),'0123456789ABCDEF')-1) ), 
            SUBSTR(TEMP_STR, 3, LEN - 2),
            LEN - 2);
     END WHILE;
     RETURN RETURN_STR;
   END 
@

HEXTOSTR in DB2

The output is a string format and not binary.

CREATE FUNCTION HEXTOSTR( INSTR VARCHAR(32672) )
     RETURNS VARCHAR(16336)
     DETERMINISTIC EXTERNAL ACTION CONTAINS SQL
   BEGIN ATOMIC
     DECLARE RETURN_STR, TEMP_STR VARCHAR(16336) DEFAULT '';
     DECLARE LEN INT;
     IF INSTR IS NULL THEN
       RETURN NULL;
     END IF;
     SET (TEMP_STR, LEN) = (INSTR, LENGTH(INSTR));
     IF MOD( LEN,  2 ) = 1 THEN
       RETURN RAISE_ERROR('22018', 'HEXTORAW');
     END IF;
     WHILE LEN > 0 DO
       SET (RETURN_STR, TEMP_STR, LEN) 
         = (RETURN_STR ||  CHR((LOCATE(UCASE(SUBSTR(TEMP_STR,1,1)),'0123456789ABCDEF')-1) *16
                             + (LOCATE(UCASE(SUBSTR(TEMP_STR,2,1)),'0123456789ABCDEF')-1) ), 
            SUBSTR(TEMP_STR, 3, LEN - 2),
            LEN - 2);
     END WHILE;
     RETURN RETURN_STR;
   END 
@