CODEWITHSUNDEEP
snowflake-cloud-data-platform
Felipe Hoffa
Felipe Hoffa

Reputation: 59165

Does snowflake have a ‘hex’ to ‘int’ type native function?

This FAQ answer says that Snowflake doesn't have a native way to convert from hex to int, and suggests a JS alternative:

But is there a way to convert from hex to bin with pure SQL in Snowflake?

Upvotes: 3

Views: 2249

Answers (1)

Felipe Hoffa
Felipe Hoffa

Reputation: 59165

The FAQ answer is wrong (we are going to fix it soon), as there is a native way to convert hex to int in Snowflake:

select to_number('fff','XXX');
-- 4095

Note that to_number needs to have an hexadecimal format string of at least the length of the hexadecimal input. It can be longer too for safety:

select to_number('fff','XXXXXXXXXXX');
-- 4095

Or you can construct the format string out of the length of the input hex string:

select to_number(h, repeat('X', length(h)))
from (
  select 'fff' h
);
-- 4095

select to_number(h, repeat('X', length(h)))
from (
  select 'fffffffffffffffffffffffffffffff' h
);
-- 21267647932558653966460912964485513215

Upvotes: 3

Related Questions