Hexadecimal to decimal through system defined function

Question

I am working on creating one view in SAP HANA.

I have column A, Data type for A is NVARCHAR.

Values in A are something like below. I need to use only last 5 digits and convert it into decimal.

A  
000000000000000000000000000EF80A
000000000000000000000000000EF812
000000000000000000000000000EF80E
000000000000000000000000000EF809
000000000000000000000000000EF80B
000000000000000000000000000EF80C
000000000000000000000000000EF80D

I made use of function

Select HEXTOBIN(0xEF80A) from dummy;

This gave me required result.

However 0x in above query is notation to mark number (EF80A) as hexadecimal.

Whenever I have to fetch 5 last digit dynamically, I am not able to assign 0x notation.

I tried following:
1) substr last 5 digits of A and concat it with 0x... This did not work, as '0x'is considered as string while it is just notation.

select distinct '0x' || right(A,5 )  from dummy;

Can someone help as to how I give 0x with last 5 char of column A to mark it hexadecimal?

Are there any direct function available for this conversion without user defined function?

score 0 · Answer 1 · answered Apr 12 '17 at 02:08

The 0x... notation for hexadecimal numbers and the X'...' for strings are only valid for typed literals. E.g. 0xEF80A explicitly types the literal a number given in hexadecimal notation. Internally, the number is of course dealt with as if you would've given an integer.
In order to be able to apply this to existing strings, a hex-string-to-number conversion function is required and SAP HANA doesn't come with one on board.

I've posted an example implementation for such a function here https://archive.sap.com/discussions/thread/3652555

To make it easy, here's it again:

drop function hexstr2int;
CREATE FUNCTION hexstr2int (IN i_hex VARCHAR(2000)) 
RETURNS o_result BIGINT  
LANGUAGE SQLSCRIPT  
SQL SECURITY INVOKER
READS SQL DATA
AS
BEGIN
  DECLARE   pos INTEGER := 1;
  DECLARE   hex_len INTEGER;
  DECLARE   current_digit VARCHAR(1);
  DECLARE   current_val INTEGER;
  DECLARE   result BIGINT := 0;
    DECLARE   tmphex VARCHAR(2000);

    DECLARE hexstr2int CONDITION FOR SQL_ERROR_CODE 10001;
  DECLARE EXIT HANDLER FOR hexstr2int RESIGNAL;
    -- some sanitation
    tmphex := UPPER(:i_hex);
    hex_len := LENGTH(:tmphex);
   WHILE :pos <= :hex_len DO
        result := :result * 16;
        current_digit := SUBSTR(:tmphex, :pos, 1);
        -- format checking
       IF NOT ((:current_digit >= 'A' and :current_digit <= 'F') or
                  (:current_digit >= '0' and :current_digit <= '9')) THEN
           SIGNAL hexstr2int SET MESSAGE_TEXT = 
                 'Invalid hex cipher: ' || :current_digit || ' at position ' || :pos;
        END IF;
        current_val := MOD(to_number(to_binary(:current_digit)),30);
        IF :current_val >= 11 THEN
             result := :result + :current_val - 1;
        ELSE
             result := :result + :current_val;
        END IF;
        pos := :pos + 1;
   END WHILE;
  o_result := :result;
END;

Hexadecimal to decimal through system defined function

1 Answers1