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

Question

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

• https://community.snowflake.com/s/article/faq-does-snowflake-have-a-hex-to-int-type-function

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

Answer 1

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