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?

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.