# Convert hexadecimal values to a decimal

XMLWordPrintable

#### Details

• Type: How To
• Status: Obsolete
• Affects Version/s: None
• Fix Version/s: None
• Component/s:
• Labels:
None
• Solution:
Hide

There is currently no builtin solution for this yet, but it's part of our roadmap (EXASOL-1162).

Until then, you have to do conversions manually, for example using a scalar pl/SQL function like this:

```CREATE OR REPLACE FUNCTION hex2dec (hexnum IN VARCHAR(30))
RETURN DECIMAL(36,0)
IS
i INTEGER;
digits INTEGER;
res DECIMAL(36,0);
current_digit CHAR(1);
current_digit_dec DECIMAL(2,0);
BEGIN
res := 0;
digits := LENGTH(hexnum);
FOR i IN 1..digits LOOP
current_digit := lower(SUBSTR(hexnum, i, 1));
IF current_digit IN ('a','b','c','d','e','f') THEN
current_digit_dec := ASCII(current_digit) - ASCII('a') + 10;
ELSE
current_digit_dec := TO_NUMBER(current_digit);
END IF;
res := (res * 16) + current_digit_dec;
END LOOP;
RETURN res;
END
/
```

Please note that EXASolution's decimal type is limited to 36 signed digits, which makes this smaller than 128 bit, in fact even a little bit smaller than the 30 hex-chars (15 data bytes) we allow in the above script.

Show
There is currently no builtin solution for this yet, but it's part of our roadmap ( EXASOL-1162 ). Until then, you have to do conversions manually, for example using a scalar pl/SQL function like this: CREATE OR REPLACE FUNCTION hex2dec (hexnum IN VARCHAR (30)) RETURN DECIMAL (36,0) IS i INTEGER ; digits INTEGER ; res DECIMAL (36,0); current_digit CHAR (1); current_digit_dec DECIMAL (2,0); BEGIN res := 0; digits := LENGTH (hexnum); FOR i IN 1..digits LOOP current_digit := lower (SUBSTR(hexnum, i, 1)); IF current_digit IN ( ' a ' , 'b' , ' c ' , 'd' , 'e' , 'f' ) THEN current_digit_dec := ASCII(current_digit) - ASCII( ' a ' ) + 10; ELSE current_digit_dec := TO_NUMBER(current_digit); END IF ; res := (res * 16) + current_digit_dec; END LOOP ; RETURN res; END / Please note that EXASolution's decimal type is limited to 36 signed digits, which makes this smaller than 128 bit, in fact even a little bit smaller than the 30 hex-chars (15 data bytes) we allow in the above script.
• Category 1:
SQL - Data types

#### People

• Assignee:
Captain EXASOL
Reporter:
Captain EXASOL