mysql conv(),雪花中的CONV（）函数

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

解决方案

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)));

$$;