convert hexadecimal to decimal in snowflake

user_15852457 0
Viewed 670

I am trying to convert hexadecimal FFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFF to decimal in snowflake. expected output is 340282366920938463463374607431768211455. (it holds 39 digits) when i tried using the snowflake java function like below, the output is in exponential format. The problem is snowflake number data type supports 38 digits.

create or replace function js_hextoint (s string) 
returns double language JAVASCRIPT 
 as 
 'if (S !== null)
 {
 output = parseInt(S, 16);
 }
 return output

output is -3.40282366920938e+38

but the expected output is 340282366920938463463374607431768211455.

how can I get the expected decimal conversion?

snowflake-cloud-data-platform user-defined-functions hex java
2 Answers

The expected output is outside the number range in Snowflake:

select 340282366920938463463374607431768211455;

-- Error line 1 at position 7 Integer literal is out of representable range: 340282366920938463463374607431768211455

You'll notice that you don't get the expected output if you do this outside Snowflake in pure JavaScript too.

But, if you really want to get the number in its full representation -- you can use a Java UDF:

create or replace function java_hextoint (s string) 
returns string 
language java 
handler='MyClass.x'
as $$
import java.math.*;
class MyClass {
  public static String x(String s) {
      return new BigInteger(s, 16).toString();
  }
}
$$;

select java_hextoint('FFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFF');

enter image description here

user_1324380

Felipe Hoffa is very much helpful

user_158524570
Related