How to convert hexadecimal to decimal on DB2 Code Answer

Hello Developer, Hope you guys are doing great. Today at Tutorial Guruji Official website, we are sharing the answer of How to convert hexadecimal to decimal on DB2 without wasting too much if your time.

The question is published on by Tutorial Guruji team.

I have a field in a db2 database which is on hexadecimal format i.e 0x0a which is number 10 in decimal format. The hex field’s datatype is char(1) for bit data.

hex(myfield) gives me the hexadecimal 0A

How can i convert 0x0a to 10 in a query on db2?

I have tried: cast(hex(myfield),integer) and int(hex(myfield)) with no luck.

Is it possible?

Answer

AFAIK, there is no such single function built into DB2 that would perform that conversion, but there is a blog post showing how to define such a function. The following function is taken from that article:

--#SET TERMINATOR @
CREATE OR REPLACE FUNCTION HEX2INT(str VARCHAR(8))
RETURNS INTEGER
SPECIFIC HEX2INT
DETERMINISTIC NO EXTERNAL ACTION CONTAINS SQL
BEGIN ATOMIC
  DECLARE res INTEGER  DEFAULT 0;
  DECLARE pos INTEGER DEFAULT 1;
  DECLARE nibble CHAR(1);
  WHILE pos <= LENGTH(str) DO
    SET nibble = SUBSTR(str, pos, 1);
    SET res = BITOR(CASE WHEN BITAND(res, 134217728) != 0
                         THEN BITOR(16 * BITANDNOT(res, 134217728),
                                    -2147483648)
                         ELSE 16 * res END,
                    CASE nibble
                         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 'a' THEN 10
                         WHEN 'B' THEN 11
                         WHEN 'b' THEN 11
                         WHEN 'C' THEN 12
                         WHEN 'c' THEN 12
                         WHEN 'D' THEN 13
                         WHEN 'd' THEN 13
                         WHEN 'E' THEN 14
                         WHEN 'e' THEN 14
                         WHEN 'F' THEN 15
                         WHEN 'f' THEN 15
                         ELSE RAISE_ERROR('78000', 'Not a hex string') 
                         END),
        pos = pos + 1;
  END WHILE;
  RETURN res;
END
@
--#SET TERMINATOR ;

There are more functions for various conversion operations described.

We are here to answer your question about How to convert hexadecimal to decimal on DB2 - If you find the proper solution, please don't forgot to share this with your team members.

Related Posts

Tutorial Guruji