Uploaded image for project: 'Solution Center'
  1. Solution Center
  2. SOL-138

Convert hexadecimal values to a decimal

    Details

    • Type: How To
    • Status: Obsolete
    • Affects Version/s: None
    • Fix Version/s: None
    • Component/s: EXASolution
    • 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

      Attachments

        Activity

          People

          • Assignee:
            CaptainEXA Captain EXASOL
            Reporter:
            CaptainEXA Captain EXASOL
          • Votes:
            0 Vote for this issue
            Watchers:
            1 Start watching this issue

            Dates

            • Created:
              Updated:
              Resolved: