CONV() function in snowflake

Question

I'm trying to find equivalent of the CONV() MySQL function in Snowflake, but I can't seem find it.

I'm trying to do the following in Snowflake:

SELECT CONV('39ix75wf3aor7',36,10)

The result should be 15468921890196183763

Answer 1

I wrote a UDF to do what CONV() does. It works, but unfortunately Javascript variables don't support numeric precision for as large as your sample.

This will work for smaller inputs, but for your large Base36 input the following happens:

15468921890196183763 --should be this result

15468921890196185000 --Javascript native variables do not have that level of precision

In case you find it useful for smaller values to covert from one base to another, here it is:

create or replace function CONV(VALUE_IN string, OLD_BASE float, NEW_BASE float)
returns string
language javascript
as
$$
    // Usage note: Loses precision for very large inputs
    return parseInt(VALUE_IN, Math.floor(OLD_BASE).toString(Math.floor(NEW_BASE)));
$$;