雪花中的CONV()函数 [英] CONV() function in snowflake

问题描述

我试图在Snowflake中找到与CONV() MySQL函数相当的功能，但我似乎找不到它.

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)

结果应为15468921890196183763

推荐答案

我编写了一个UDF以完成CONV()的工作.它可以工作，但是不幸的是，JavaScript变量不支持与样本一样大的数值精度.

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.

这将适用于较小的输入，但是对于较大的Base36输入，会发生以下情况:

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

15468921890196183763-应该是这个结果

15468921890196183763 --should be this result

15468921890196185000 --Javascript原生变量没有这种精度水平

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

这篇关于雪花中的CONV()函数的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！