HEX String to INTEGER conversion

[Former Member]

Hi,

I would like to convert a hex string representation to the corresponding INTEGER value.

I've tried:

select cast(hextobin('A') as int) from dummy;

which yields:

Could not execute 'select cast(hextobin('A') as int) from dummy' in 34 ms 902 µs .

SAP DBTech JDBC: [339]: invalid number: '0A' at function to_int()

I know that the next statement works, but 0xA is already binary and I guess implicite typeconversion just parses the token to integer without typecasting.

select cast(0xA as int) from dummy;

Any ideas?

Many thanks in advance,

Bodo

PS: The background is that I'm searching a way to hash a string value with hash_sha256 to number format (like ORA_HASH in oracle db). I've not found a way to typecast varbinary to number. Instead I try to convert to hex representation first and then convert to integer.

[Former Member · Accepted solution]

To help myself with an interim solution I came up with the following procedure to convert a HEX string to BIGINT:

CREATE PROCEDURE hex2int (IN i_hex VARCHAR, OUT o_result BIGINT)  
LANGUAGE SQLSCRIPT  
SQL SECURITY INVOKER AS
  pos INTEGER := 1;
  hex_len INTEGER;
  current_digit CHAR;
  current_val INTEGER;
  result BIGINT := 0;
BEGIN
  DECLARE hex2int CONDITION FOR SQL_ERROR_CODE 10001;
  DECLARE EXIT HANDLER FOR hex2int RESIGNAL;
  hex_len := LENGTH(:i_hex);
  WHILE pos <= hex_len DO
       result := result * 16;
       current_digit := SUBSTR(:i_hex, pos, 1);
       -- format checking
       IF NOT ((current_digit >= 'A' and current_digit <= 'F') or
              (current_digit >= '0' and current_digit <= '9')) THEN
          SIGNAL hex2int 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;

Test Call:

CALL hex2int('FF', ?);

Any hints to express this typconversion directly without using a stored procedure?

[lbreddemann · Accepted solution]

How about a user defined function instead?

Same code, nicer call option:

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;

select hexstr2int('A0feAadd') from dummy

HEXSTR2INT('A0feAadd')

2701044445           

- Lars

[lbreddemann · Accepted solution]

geeze... the new SQL formatting really does not enhance the code display ..

[Former Member · Accepted solution]

Yes, thanks for the input and this is exactly what I tried to do, but it yields:

Could not execute 'CREATE FUNCTION hexstr2int (IN i_hex VARCHAR(2000)) RETURNS o_result BIGINT LANGUAGE SQLSCRIPT SQL ...' in 1.603 seconds .

SAP DBTech JDBC: [7]: feature not supported: Scalar UDF does not support the type as its input/output argument: Varchar3

on the HANA version I currently use in the cloud. It seems that older HANA versions only support primitive datatypes for scalar UDFs (so varchar is not supported).

Besides, my hope was that HANA provides a speedy/internal datatype conversion out of the box (or even a numerical hash function) but it seems that this hope was in vain. I can imagine many use-cases where I would want to have a numeric hash based partitioning. This seems to be somewhat outdated in the context of todays memory sizes where 32 bytes space to store a hash value is ok even if I only plan to have 8 partitions with uniform distribution.

Again, Lars I really appreciate your input and in fact my procedure doesn't fit my need but your UDF does. But it is currently not supported by the HANA cloud version.

Any clever tricks to solve this with HANA internal typeconversion?

Best regards,

Bodo

[lbreddemann · Accepted solution]

Correct, character-type parameters for UDFs are supported as of SPS 8.

I thought the cloud version would be on SPS 8 by now - but apparently it's not

I see the use case, but there's currently no string-unpacking data type conversion available, except for date/time data types.

About the partitioning: what exactly do you want to do?

Distribute data evenly into table partitions?

Partition type HASH does that already automatically.

- Lars

[Former Member · Accepted solution]

My current use-case is in the context of a PoC where I generate testdata with configurable data volumes in a generic but deterministic way (a non real-life scenario). I know that HANA provides internal partitioning out of the box (e.g., the PARTITION BY HASH (a) PARTITIONS 12 option for table creation). Is there a way to explicitly apply partition pruning in a select statement, e.g. to enable for parallel reading with an external ETL Tool? In Oracle I would use a PARTITION clause to explicitly prune a query to a certain hash partition.

I admit that this might not be a common use-case and this may not be needed in many real-life scenarios. But in the past explicite hashing/pruning/parallelization came handy in some performance critical ETL situations.

Many thanks,

Bodo

[lbreddemann · Accepted solution]

There's no need to explicitly activate partition pruning in SAP HANA. Whenever the base table is partitioned and the WHERE clause contains a matching clause SAP HANA will automatically use the partition pruning.

If you change data, the new data is put into the affected partition only, too.

has a blog post on SAPHANA.COM that summarizes techniques to improve data loading speed on SAP HANA - partitioning being one of them.

Speaking from personal experience I'd try to avoid taking Oracle-tuning techniques and apply them to SAP HANA. Usually this way of thinking leads to a situation, where some important options are left untouched which can lead to not-so-great-performance.

Rethinking the ETL process for the new platform yields better results.

- Lars

[Former Member · Accepted solution]

Thanks Lars!

By all means I will leave automatic partition pruning to the optimizer if possible. And I totally agree that l have to rethink ETL and model design completely without assuming techniques that were valid in other specific scenarios. This is exactly what I currently evaluate in my current PoC. So we're getting a little bit offtopic now and I thank you for the provided links and information to my question!

Best regards (topic is closed from my side),

Bodo

[former_member209468 · Accepted solution]

Thanks Lars

is there any reverse function like Int to hexstring ?

Thanks in advance

[former_member209468 · Accepted solution]

I did it

CREATE FUNCTION int2hexstri (IN int_hex int)

RETURNS o_result VARCHAR(2000)  

LANGUAGE SQLSCRIPT  

SQL SECURITY INVOKER

READS SQL DATA

AS

BEGIN

declare   currentvalue integer :=0;

declare boucle integer;

declare str nvarchar(50);

declare str1 nvarchar(50);

currentvalue := mod(:int_hex,16);

str:=

case when

    currentvalue <=9

    then cast ( :currentvalue as nvarchar )

    when currentvalue = 10

    then  'A'

    when currentvalue = 11

    then 'B'

    when currentvalue = 12

    then  'C'

    when currentvalue = 13

    then 'D'

    when currentvalue = 14

    then 'E'

    else 'F'

    END;

boucle := :int_hex/16;

   WHILE boucle / 16  != 0 DO

        currentvalue := mod(boucle,16);

        str1 :=

       case when

    currentvalue <=9

    then cast ( :currentvalue as nvarchar )

    when currentvalue = 10

    then  'A'

    when currentvalue = 11

    then  'B'

    when currentvalue = 12

    then 'C'

    when currentvalue = 13

    then  'D'

    when currentvalue = 14

    then  'E'

    else  'F'

    END;

  str := concat(str1, str);

  boucle := :boucle/16;

   END WHILE;

o_result := :str;

END; 

[former_member209468 · Accepted solution]

Hi Lars

I get sometimes this error when i call this function hexstr2int in other function: SAP DBTech JDBC: [430]: invalidated procedure: detected cyclic dependency

what does it mean ? and how can i fix it ?

Thanks

[lbreddemann · Accepted solution]

I don't know your code - so I cannot tell you what is going on there

Cyclic dependencies are usually detected, when you create/alter/drop objects in a procedure in which you also use these objects.

- Lars

[Vlado]

This looks like a core HANA DB question, not Cloud Platform related => moved to HANA dev center.

[Former Member]

Yes, sorry that I failed to hit the right forum.